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
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
