Hello, I am attempting to conduct a simple test to retrieve data from an Oracle (9i) CLOB from a perl script on a windows platform.

The error I receive is: DBD::Oracle::st fetchrow_array failed: ORA-01406: fetched colum value was truncated (DBD: ORA-01406 error on field 1 of 2, ora_type 112) at simple.pl line 45.

This is on a CLASSIFIED system but I will type in what I think is pertinent:

#--------- VERSIONS ----------------------------
Perl: ActiveState 5.6.1 (since had issues with DBI and 5.8.3)
DBI version: 1.34
DBD::Oracle: 1.06
Oracle: 9i
Oracle.pm (v 1.80 I think)
#---------- CODE SNIPPETS -----------------------

use DBI;
OpenDatabaseConnections(); # nothing special with RaiseError = 1
...
$dbh->{LongReadLen} = 512 * 1024; # prior to prepare, right?
$dbh->{LongTruncOk} = 1;  # frustration - like to see "something"
my $sql = "select chardata from CLOB_TEST where id = 2";
my $sth = $dbh->prepare($sql, {ora_auto_lob => 0 } );
$sth->execute();
$char_locator = $sth->fetchrow_array() 
  or die "ERROR: " . $sth->errstr . "\n";
[download]

... ABOVE FAILS (this is the problem I would like to solve)... ... but if all goes well, hopefully continue as below...

my $chunksize = 1024;
my $offset = 1;
my $data = $dbh->ora_lob_read($char_locator, $offset, $chunksize)
  or die "ERROR: " . $sth->errstr . "\n";
[download]

#-------------------------------------------------
I pasted alot of data into this CLOB field using TOAD for my test. I get the ORA-01406 truncation error every single time I attempt to FETCH from a CLOB that has "alot" of data. If I do a test against a CLOB that holds a "short sentence" the FETCH WILL WORK, but it will still FAIL on the $dbh->ora_lob_read(...) ...but that may be a different issue - one step at a time.

At any rate, I have researched numerous examples of this (including CPAN examples) and it looks like I should be indicating a LARGE "LongReadLen" PRIOR to my "prepare". I am doing that.

I guess I am missing something fundamental? Again, I have searched for quite a while on the internet looking for the solution to this issue. Not sure if I have some "version conflict" or not. All other Oracle DBI code is functioning quite well. It is just recently that I have needed to implement support for some CLOB datatypes. I want to ultimately read the data in chunks, then concatenate it. I have a crude hack that accomplishes this now, but I felt the correct way was to use the DBI lob functionality.

I am willing to admit I am a moron, :-). Any help you all could provide would be most appreciated.

Thank you in advance,
Greg
ps. If I have left out any information critical to resolving this problem, please let me know.

#--------------------------------------------------------------
PART 2: If you already know the answer to my question above...-
#---------------------------------------------------------------

If you are able to provide the answer to the question above, here is "part 2" relating to:

$dbh->ora_lob_read(...)

When I execute a successful FETCH from the CLOB field (which only occurs when data is "small"), then the "ora_lob_read" fails as well. The error I receive in this case is:

Can't locate object method "ora_lob_read" via package "DBI::db" (perhaps you forgot to load "DBI::db"?) at simple.pl line 58.

THANKS.