Martin Evans wrote:
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 guess this must be one of these circumstances. When you try to get a
LOB through a SP.
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.
Ah 'Prease to remembering frist wurle of robes'
The value of an Oracle LOB column is not the content of the LOB, It's a
'LOB Locator' .
So every time you select a lob column you get a locater, DBD::Oracle
just hides that fact from you. In this case the all DBD::Oracle know is
that you are selecting a cursor (SP) which id does, I guess it does not
have the smarts to know that the next level down is a locator so it
just returns that not the actual value locator points to.
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.
That might be a little dated there were a number of patches to fix that
I guess the pod has yet to be cleaned up.
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
Ok Martin I will try and get a look at this today. The change may be
trivial but I aint promising anything.
cheers
John sCoes
Great marting.
