Re: Problems with empty/NULL lobs

Martin Evans wrote:

Most likely you are running into the problem of  lob locater state

A lob field can have two states

1) Null meaning no Lob locater present
2) Lob Locater present (it might point to a null LOB of course)

You might try and change your code so that is tests for null in the lob 
field before you try and fetch it

Hope this helps?

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