Oracle CLOB using DBI

[Ken Chesak]

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";
}

Thanks

[Ron Reidy]

What error (if any) are you getting? Did you 'perldoc DBD::Oracle' and
look at the section entitled 'Handling LOBS'? Did you set
LongReadLen/LongTruncOK on the $dbh?

--
Ron Reidy
Oracle DBA

Ken Chesak wrote:

> 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";
> }
>
> Thanks

--
Ron Reidy
Oracle DBA

[Markus Stueker]

On 24 Jun 2003 13:55:40 -0700, [email]datavector@hotmail.com[/email] (Ken Chesak)
wrote:

>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";
> }
>
>Thanks

[Ron Reidy]

From metalink Note 19792.1:

Error: ORA 3115

Text: unsupported network datatype or representation

-------------------------------------------------------------------------------

Cause: A user bind or define, or an Oracle function, is not supported by
this

heterogeneous SQL*Net connection.

Action: Upgrade the older version of Oracle and try again.

Do you have an old Oracle install on the machine the code is running on?
If so, this is your problem.

--
Ron Reidy
Oracle DBA

Ken Chesak wrote:

> Would you email my hotmail account again, I delete by mistake since
> 99% is junk mail.
>
> I get the following error,
>
> DBD::Oracle::db prepare failed: ORA-03115: unsupported network
> datatype or repre
> sentation (DBD: odescr failed) at clob.pl line 26.
>
> It runs if I use dbms_lob.substr( comments, 30000, 1).
>
> $dbh->{LongReadLen} = 512 * 1024 ;
> $dbh->{LongTruncOk} = 1;
>
> $sth1 = $dbh->prepare(q{
> SELECT cd_type, comments
> FROM comments WHERE id_comment = 53
> });
>
> $sth1->execute();
>
> while ( ($name, $data) = $sth1->fetchrow_array ) {
>
> print "row = $name $data \n";
>
> }

--
Ron Reidy
Oracle DBA