Re: return CLOB via Perl/DBI

On 25 Jun 2003 08:41:58 -0700, datavector_at_hotmail.com (Ken Chesak) wrote:

>Using the following code I get the error,
>
>DBD::Oracle::db prepare failed: ORA-03115: unsupported network
>datatype or repre
>sentation (DBD: odescr failed) at clob.pl line 26.

 Sounds a lot like you've got a DBD::Oracle linked against Oracle 7 libraries, which wouldn't know about LOBs. Where did you get it from? Did you compile it yourself, or did you get a pre-built version?

 A search for 'DBD::Oracle ORA-03115' gives:

http://archive.develooper.com/dbi-users@perl.org/msg14452.html

 ... which confirms that idea. The 'odescr' is also a giveaway, since it's OCIDescribe in 8i and beyond.

 The following works with DBD::Oracle when it's correctly linked to 9i libraries (and would be fine against 8i too):

SQL> select id, dbms_lob.getlength(clobval) from clobtab;

ID DBMS_LOB.GETLENGTH(CLOBVAL)

-- ---------------------------
 1                      400000

#!perl -w
use strict;
use DBI;

my $dbh = DBI->connect('dbi:Oracle:dev92', 'test', 'test',

		       {
			RaiseError  => 1,
			AutoCommit  => 0,
			LongTruncOk => 1,
			LongReadLen => 500000,
		       }
		      ) or die DBI->errstr;

my $clobval = $dbh->selectrow_array('select clobval from clobtab where id=1');

print length($clobval);

$dbh->disconnect;

__END__  Outputs:

$ perl clob.pl
400000

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)