Re: Problem with DBD::Oracle ora_auto_lob not working

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
.