Problems with empty/NULL lobs

Hi,

I'm experiencing a problem in DBD::Oracle calling a procedure which
returns a lob but sometimes the lob is not set. We use ora_auto_lob = 0
so we can retrieve the lob ourselves but I cannot see how to know the
lob is empty and calling ora_lob_length returns an error:

DBD::Oracle::db ora_lob_length failed:  (DBD INVALID_HANDLE:
OCILobGetLength) at empty_lob.pl line 26.

The following code illustrates the problem:


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});
}

The output is:

$ 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.

Any ideas how to detect an empty lob in this case?

BTW, there are 3 typos in the pod in the following sentence:

"A word of catution when using the data retruned from an ora_lob_read in
a condtional statement. for example if the code below;"

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com