Home › Perl › DBD::Oracle › Issues

Binding and retrieving NULL lobs from procedures

23 February, 2010 - 20:24 — martinjevans
Just hit a rather annoying problem in DBD::Oracle 1.24a today. Our application contains all the logic in database procedures and in some cases database result-sets are returned to our Perl code via a reference cursor. Due to problems with ora_auto_lobs not working on reference cursors in previous DBD::Oracle releases we generally use Data Interface for Lob Locators where ora_lob_length returns a lobs sie and ora_lob_read is used to retrieve it. As a result we carried this method on to our other uses of lobs. For instance, when we call a procedure that may return a lob we disable ora_auto_lobs, bind the lob as a parameter then use ora_lob_length and ora_lob_read to obtain the lob. This works ok for most instances except the one I hit today. In this case the called procedure sometimes does not return anything in the procedure output parameters i.e., we call the procedure expecting a returned lob but nothing in the procedure sets the output bound lob parameter. Unfortunately it fails. The following code demonstrates this:
use strict;
use warnings;
use DBI;
use DBD::Oracle qw(:ora_types);
use Data::Dumper;

my $h = DBI->connect;

$h->do(<<'EOT');
create procedure p_mje(plob OUT NOCOPY clob) AS
BEGIN
NULL;
END;
EOT

my $s = $h->prepare(<<'EOT', {ora_auto_lob => 0, ora_check_sql => 0});
begin p_mje(?); end;
EOT

my $lob;
$s->bind_param_inout(1, \$lob, 20, {ora_type => ORA_CLOB});
$s->execute;

print Dumper([$lob]);

# following line errors
my $len = $h->ora_lob_length($lob);

END {
    $h->do(q{drop procedure p_mje});
}
and this outputs:
$ perl empty_lob.pl
$VAR1 = [
          bless( do{\(my $o = 168781336)}, 'OCILobLocatorPtr' )
        ];
DBD::Oracle::db ora_lob_length failed:  (DBD INVALID_HANDLE:
OCILobGetLength) at empty_lob.pl line 26.
So it appears we get a valid lob locator back but we cannot tell it points to a NULL lob and if we dare call any of the lob methods like ora_lob_length it fails. So how do we ascertain this lob locator points at a null before calling ora_lob_length?

Trackback URL for this post:

http://martin-evans.me.uk/trackback/59