Hi Martin I found the bug (well actually an omission) and fixed it so
now It will return the data you expect.
I committed the changes to Trunk
http://svn.perl.org/modules/dbd-oracle/trunk so if you will try the code
from there and see if it works for you now.
Seem the not all the attributes for the SP were not being passed along
so in actual fact it was only need the addition of 6 lines of code. As
is normal with anything to do with DBD::Oracle (well actully C in
general) it took 2 hours to find out where to put those lines fro the 2
min change.
cheers
John Scoles
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 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
