Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery

Oracle DBI CLOB Issue: fetch Column TRUNCATED

by Anonymous Monk
on Apr 09, 2006 at 20:18 UTC ( #542175=perlquestion: print w/replies, xml ) Need Help??

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

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 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 (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";
... 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";

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,
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:


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 line 58.


Replies are listed 'Best First'.
Re: Oracle DBI CLOB Issue: fetch Column TRUNCATED
by helphand (Pilgrim) on Apr 09, 2006 at 20:59 UTC

    Can't answer part 1, but on part 2, I believe the following note would apply since you are using DBI version 1.34:

    LOB Locator Methods The following driver-specific methods let you manipulate "LOB Locators +". LOB locators can be selected from tables directly, if the ora_auto +_lob attribute is false, or returned via PL/SQL procedure calls. (If using a DBI version earlier than 1.36 they must be called via the +func() method. Note that methods called via func() don't honour Raise +Error etc, and so it's important to check $dbh->err after each call. +It's recommended that you upgrade to DBI 1.38 or later.)


    Note You may want to look into the really old version of DBD::Oracle (1.06 versus current version 1.17) as possibly part of your issue with part 1, the changelogs between 1.06 and 1.17 have references to the LOB stuff you are using.


      Thanks. You may be right. I was just reviewing the CHANGE LOG (something I have never really done). I see the following for DBD::Oracle:

      =head1 Changes in DBD::Oracle 1.07 5th June 2001
      Fixed error in 'LOB value truncated' error thanks to Paul Walmsley.

      So they have apparently corrected some things dealing with LOB truncation. I did not realize how old this DBD::Oracle module apparently is. This may or may not be the solution but I suspect since I am doing some rather straight-forward calls, the versions I have probably cannot support it.
      I will review versions of DBD::Oracle and DBI and look to upgrade before banging my head against this anymore.


        Instead of '$dbh->{LongReadLen} = 512 * 1024' you can use $dbh->LongReadLen(512 * 1024). This may work for older version

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://542175]
Approved by Corion
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (7)
As of 2019-12-06 19:14 GMT
Find Nodes?
    Voting Booth?
    Strict and warnings: which comes first?

    Results (157 votes). Check out past polls.