John Scoles wrote:
Ouch nasty one Martin.
But I think that DBD oracle is doing the correct thing here.
Well that is what I'm after clarification on. The pod for ora_auto_lob
says "retrieves the contents of the CLOB or BLOB column in most
circumstances". What are the circumstances it does /not/ retrieve the
clob/blob data and returns a lob locator instead?
I think when you bury the 'SELECT x from martin; ' in the SP like this
you are essentially only ever going to get a lob locater back as that is
what you are asking for.
I don't understand your comment here. I never asked for a lob locator, I
asked for the contents of column 'x' and if I run this select in Perl I
get the clob contents back (presumably because ora_auto_lob is on by
default and works on normal statement handles). It is only when I run
the select in an oracle procedure and return the cursor which gets
magicked into a DBI statement handle and then run a fetch on that
statement that I get a lob locator.
Of couser you could use all the neat lob function in DBD Oracle on it
like 'ora_lob_read', 'ora_lob_write' etc, which of course kind of
defeats the purpose of this exercise.
Actually, I do not mind running ora_lob_read to get my data back from
the lob locator (although I'd prefer ora_auto_lob did it for me) but my
data is utf8 and the pod says:
"Warning: Currently multi-byte character set issues have not been fully
worked out. So these methods may not do what you expect if either the
Perl data is utf8 or the CLOB is a multi-byte character set (including
uft8). The current behaviour in these situations may not be correct and
is subject to change."
which suggested to me that you can't get utf8 data back from
ora_lob_read. What I am actually trying to do is hide the SQL and any
schema from the perl by placing it in the database in a package, I'm not
worried how I get the data out when the procedure is called.
If you are using DBD:ORacle 1.21 you would try using the
ora_type=>SQLT_CHR or ora_pers_lob.
I did try using ora_pers_lob but it made no difference.
I going to release a new version of
the code next week 1.22 that have a number of LOB improvements that
might help.
Excellent, I look forward to that.
I will see if I can recreate you problem today right not I am little
busy with real work.
Thanks. I have added to my test case a little to actually write utf8
data and get it back - it would seem that the pod warning with respect
to utf8 data not coming back from ora_lob_read /may/ be out of date. See
new code example below.
use DBI (data_diff);
use warnings;
use strict;
use Data::Dumper;
use DBD::Oracle qw(:ora_types);
use Encode;
use charnames ':full';
my $h = DBI->connect('dbi:Oracle:XE', 'xxx', 'xxx');
eval {$h->do(q/drop table martin/);};
$h->do(q/create table martin (x clob)/);
my $data = "\x{263a}xxx" . chr(0x05d0) . "\N{ARABIC LETTER ALEF}";
print "Length of input data: " . length($data) . "\n";
$h->do(q/insert into martin values (?)/, undef, $data);
my $createproc = << 'EOT';
CREATE OR REPLACE PROCEDURE p_martin(pc OUT SYS_REFCURSOR) AS
l_cursor SYS_REFCURSOR;
BEGIN
OPEN l_cursor FOR
SELECT x from martin;
pc := l_cursor;
END;
EOT
$h->do($createproc);
my $s = $h->prepare(
q/begin p_martin(?); end;/, {ora_auto_lob => 1});
my $sth;
$s->bind_param_inout(1, \$sth, 50000,
{ora_type => ORA_RSET});
$s->execute;
my ($lobl) = $sth->fetchrow;
print Dumper($lobl);
my $length = $h->ora_lob_length($lobl);
print "lob length: $length\n";
my $cdata = $h->ora_lob_read($lobl, 1, $length);
print data_diff($data, $cdata);
which prints:
Length of input data: 6
$VAR1 = bless( do{\(my $o = 142150356)}, 'OCILobLocatorPtr' );
lob length: 6
and does not print anything for data_diff suggesting to me the input and
output data are the same.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
Martin Evans wrote:
Hi,
I am in the process of moving some select SQL which was in Perl into
functions and procedures in an oracle package which return a cursor
the perl can read i.e., to hide the SQL from outside the database.
Some of these select statements read clobs.
create table martin (x clob);
In perl we were doing:
set LognReadLen
prepare(q/select x from martin/);
execute
fetch
and this works ok, the clob is retrieved as data and not as a lob
locator.
We are now calling a procedure which issues the select and returns a
cursor. The cursor is magicked into a DBI statement handle by
DBD::Oracle but fetching on it returns a lob locator and not the data
(as before).
Have I perhaps hit the "most" in this quote from DBD::Oracle:
ora_auto_lob
If true (the default), fetching retrieves the contents of the CLOB
or BLOB column in most circumstances. If false, fetching retrieves
the Oracle "LOB Locator" of the CLOB or BLOB value.
The code below demonstrates. I thought it may be that ora_auto_lob
does not work on statement handles created for returned cursors so I
attempted to use the DBD::Oracle lob functions to get the data but
this data is UTF8 and does not come back correctly. Here again the
DBD::Oracle pod says:
Warning: Currently multi-byte character set issues have not been fully
worked out. So these methods may not do what you expect if either the
Perl data is utf8 or the CLOB is a multi-byte character set (including
uft8). The current behaviour in these situations may not be correct
and is subject to change.
Anyone got any suggestions?
use DBI;
use warnings;
use strict;
use Data::Dumper;
use DBD::Oracle qw(:ora_types);
my $h = DBI->connect('dbi:Oracle:XE', 'xxx', 'xxx');
eval {$h->do(q/drop table martin/);};
$h->do(q/create table martin (x clob)/);
my $data = 'A' x 8000;
$h->do(q/insert into martin values (?)/, undef, $data);
my $createproc = << 'EOT';
CREATE OR REPLACE PROCEDURE p_martin(pc OUT SYS_REFCURSOR) AS
l_cursor SYS_REFCURSOR;
BEGIN
OPEN l_cursor FOR
SELECT x from martin;
pc := l_cursor;
END;
EOT
$h->do($createproc);
my $s = $h->prepare(
q/begin p_martin(?); end;/, {ora_auto_lob => 1});
my $sth;
$s->bind_param_inout(1, \$sth, 50000,
{ora_type => ORA_RSET});
$s->execute;
my $out = $sth->fetch;
print Dumper($out);
which prints:
$VAR1 = [
bless( do{\(my $o = 151245220)}, 'OCILobLocatorPtr' )
];
Martin
