Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: return CLOB via Perl/DBI

Re: return CLOB via Perl/DBI

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Tue, 24 Jun 2003 23:14:47 +0100
Message-ID: <2vihfvsql7ci8cjbna6vcq4eivv5euuoeo@4ax.com>


On 24 Jun 2003 13:53:52 -0700, datavector_at_hotmail.com (Ken Chesak) wrote:

>Oracle 8.1.7, Perl 5.004_04 , DBI 1.13
>
>Does someone have a working code sample of Perl/DBI returning a CLOB
>column from Oracle. Here is what I came up with, I counld not get
>ORA_CLOB to work.
>
>$sth1 = $dbh->prepare(q{
> BEGIN OPEN :cursor FOR
> SELECT dbms_lob.substr( comments, 30000, 1)
> FROM comments WHERE id_comment = 46;
> END;
> });
>
> $sth1->bind_param_inout(":cursor", \$sth2, 30000, { ora_type
>=> ORA_RS
>ET } );
> $sth1->execute();
>
> while ( @row = $sth2->fetchrow ) {
> print "row = @row\n";
> }

 Not sure why you're setting up a cursor in PL/SQL like that; you can set LongTruncOK and LongReadLen and do a plain SELECT with the CLOB with DBD::Oracle, so long as it's not 'too long' (where 'too long' is > LongReadLen).

 If you want proper piecewise fetches for large LOBs of unknown length, the documentation points you towards Oracle::OCI so you can handle the LOB locator yourself.

 http://theoryx5.uwinnipeg.ca/CPAN/data/DBD-Oracle/Oracle.html#Handling_LOBs

 DBMS_LOB.substr returns a varchar2, so 30000 is surely an invalid length anyway within a cursor (it'd be OK within a PL/SQL stored procedure, though).

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
Received on Tue Jun 24 2003 - 17:14:47 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US