Ouch nasty one Martin.
But I think that DBD oracle is doing the correct thing here.
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.
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.
If you are using DBD:ORacle 1.21 you would try using the
ora_type=>SQLT_CHR or ora_pers_lob. I going to release a new version of
the code next week 1.22 that have a number of LOB improvements that
might help.
I will see if I can recreate you problem today right not I am little
busy with real work.
cheers
John Scoles
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
