Re: Problems with empty/NULL lobs

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