Problem with DBD::Oracle ora_auto_lob not working

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
.

Relevant Pages

  • RE: How to read CLOB return value of Stored Function in Perl
    ... no data found (DBD ERROR: ... How to read CLOB return value of Stored Function in Perl ... pid TEMP_TABLE.PID%TYPE; ...
    (perl.dbi.users)
  • How to read CLOB return value of Stored Function in Perl
    ... I am trying to read a CLOB value in Perl that is returned from an Oracle ... CREATE OR REPLACE FUNCTION f_load(id varchar2,type varchar2) ... pid TEMP_TABLE.PID%TYPE; ...
    (perl.dbi.users)
  • Re: Cursor output to CSV
    ... Then you can dump the cursor contents with an SQLPlus print ... command: print cursor_variable ... I must say that I'd use Perl to do that. ...
    (comp.databases.oracle.server)
  • QBasic Befehl Locate in Perl
    ... ich bin Neuling was Perl Programmierung betrifft und habe nun ein kleines ... Es gibt doch in QBasic den Befehl Locate, mit dem man den Cursor ... zum Ein-/Ausgeben auf der Konsole an eine bestimmte Stelle setzen kann, ...
    (de.comp.lang.perl.misc)
  • Re: save & restore cursor position in perl
    ... > cursor in perl on FreeBSD 4.10 using the escape sequences without ... FreeBSD do not seems to support it. ... When talking about cursor positions usually the Curses module is the answer. ...
    (comp.lang.perl.misc)