Re: Problems with empty/NULL lobs

The Oracle::OCI module might be useful. See lob examples in
http://cpansearch.perl.org/src/TIMB/Oracle-OCI-0.06/05dbi.t

Tim.

On Tue, Feb 23, 2010 at 07:17:20PM +0000, Martin J. Evans wrote:
> On 23/02/2010 18:48, John Scoles wrote:
> >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
> >
> How do you do that John? The lob locator is:
> 
> $VAR1 = [
>           bless( do{\(my $o = 168781336)}, 'OCILobLocatorPtr' )
>         ];
> 
> so it is not null/undef but the thing it points to may be?
> 
> I am your second case - I have a lob locator but it points to a null
> lob. How do you find out if a lob locator points to a null lob?
> 
> >Hope this helps?
> >
> Martin
> 
> >>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
>