Re: perl DBI/DBD problem, Oracle stored procedure returning CLOB

Manuela,

See below.

Manuela Mueller wrote:
>
> OS: Linux 2.4.18, Suse 8.0
> Perl: 5.6.1
> DBI: 1.20
> DBD: 1.12
> RDBMS: Oracle Standard Edition 9.2

> # specify max size of the lob
> $dbh->{LongReadLen} = 2.5 * 1024 * 1024;

Are you not setting LongTruncOK?

> $sth1->bind_param_inout(":p_mklob", \$mklob, 2048000, { ora_type => 112
> } );

Why the different sizes (2.5 * 1024 * 1024 = 2621440)?

> I tried different syntax for $sth1->bind_param_inout, with and without
> specifying ora_type, but
> the result is always the same.

You will need to always set ora_type (according to the DBD::Oracle docs).

Is there only one clob column on this table? If there are more, the DBD::Oracle docs describe the behavior for updates (maybe the same for selects?).

> (DBI::st=HASH(0x84a908c)~0x84a9254 ':p_mklob' SCALAR(0x84a33d4) 2048000
> HASH(0x84a92
> 3c))

The size you specified in bind_param_inout().
> bind :p_mklob <== undef (type 0, inout 0x84a33d4, maxlen 2048000,
> attribs: HASH(0x84a923c))
> bind :p_mklob done with ftype 112
> <- bind_param_inout= 1 at tmarkup_document.pl line 99
> -> execute for DBD::Oracle::st (DBI::st=HASH(0x84a908c)~0x84a9254)
> dbd_st_execute BEGIN (out9, lob1)...
> bind :p_mklob done with ftype 112
>
> <snipped the successfully params>
>
> out ':p_content_length' [0,0]: alen 29, piece 0
> dbd_st_execute BEGIN returned (SUCCESS, rpc1, fn34, out9)
> ERROR EVENT 1403 'ORA-01403: no data found (DBD ERROR: LOB refetch
> attempted for unsupported statement type)' on DBI::st=HAS
> H(0x84a9254)
> !! ERROR: 1403 'ORA-01403: no data found (DBD ERROR: LOB refetch
> attempted for unsupported statement type)'
> <- execute= undef at tmarkup_document.pl line 101
> <Rest snipped>
>

I think the 1403 may be related to truncation of the column (not sure about this).

> I got the impression DBD and/or DBI is interpreting the CLOB as a
> VARCHAR2 with maxsize of 4000 characters.
>

As you know, Perl variables do not necessarily map to Oracle datatypes.

> When I try to read a CLOB/BLOB via "select x from table Y", it is always
> sucessfully read.
>
> Maybe I'm on the wrong way, trying to read a CLOB returned from a stored
> procedure?

Maybe. Have you tried this w/o a SP?

Another source for this problem might be the dbi-users_at_fugue.com group.

I for one would be interested in how this turns out. Please keep us posted.

-- 
Ron Reidy
Oracle DBA