Further to my problems with lobs in Oracle the following code works fine
in DBD::Oracle 1.20 and seg faults in DBD::Oracle 1.21. The key seems to
be that the returned cursor points to 2 rows. If the do calls which
insert into the table are reduced to 1, there is no problem. Likewise,
if the result-set contains no lobs there is no problem.
I am running:
Linux Fedora Core release 5 (Bordeaux)
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
DBI 1.605
DBD::Oracle (1.20 and 1.21 - the latter seg faults)
Perl v5.8.8 built for i386-linux-thread-multi
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}";
$h->do(q/insert into martin values (?)/, undef, $data);
$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_pers_lob=>1}
my $sth;
$s->bind_param_inout(1, \$sth, 500,
{ora_type => ORA_RSET});
$s->execute;
# NOTE: seg faults here in 1.21 IF there is more than one row in the
# result-set but is ok if either a) there is only one row or b) you
# use 1.20.
while (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);
}
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
