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.

In reply to Oracle DBI CLOB Issue: fetch Column TRUNCATED by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.