Re: Problems with empty/NULL lobs

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

I tried but I don't have a full Oracle distribution just an instant
client and after an hour of hackery I got close:

o change the dirs in boot
o changed path in included h2xs
o included h2xs would not parse oci.h
o changed to latest h2xs
o latest h2xs does not seem to support -E sub,sub (removed -E)
o OCIError and ub4 missing from typemap (guessed a little for ub4)

finished with:

/usr/bin/perl /usr/share/perl/5.10/ExtUtils/xsubpp  -typemap
/usr/share/perl/5.10/ExtUtils/typemap -typemap h2xs.typemap -typemap
typemap  OCI.xs > OCI.xsc && mv OCI.xsc OCI.c
Please specify prototyping behavior for OCI.xs (see perlxs manual)
make: *** No rule to make target `XSUB.h', needed by `OCI.o'. Stop.

In the end I can change my procedure to return a reference cursor
(although that will be annoying) or just an empty clob. I wish I had
more time to look at this but I'm really struggling.

Martin

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