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

Perl script (in Win 95 environment) uses DBI to access Oracle (on a unix machine).

This (and other scripts using DBI to access the oracle DB) work fine except when a record retrieved contains more than 79 characters.

Record is retrieved by fetchrow_array inside a while: 

 while (@data = $handle->fetchrow_array() ) {

[download]

All data comes back in $data[0]

When data in DB is > 79 characters long get the following error message:

DBD::Oracle::st fetchrow_array failed: ORA-01406: fetched column value was truncated (DBD: ORA-01 406 error on field 1 of 1, ora_type 8, DBI attribute LongTruncOk not set and/or LongReadLen too small) at my_perl_script.pl line xx.

Oracle documentation suggests that a buffer is too small on client (in my perl script I assume?)

How can I set the buffer size in my perl script so I can get longer values back?

And/Or, any suggestions for a workaround?

thanks in advance ;-)

Replies are listed 'Best First'.
Re: DBI Oracle error LongReadLen too small
by blakem (Monsignor) on Nov 14, 2001 at 04:06 UTC
[reply]
Re: DBI Oracle error LongReadLen too small
by Rhose (Priest) on Nov 14, 2001 at 04:00 UTC
    I played with this and could not get it to fail -- I was not sure if a long (length) CHAR would trigger the error. Either it does not, or the fact I am using Oracle 8 is masking it.

    Oracle version: 8.1.7.2

    Play table used: 

    CREATE TABLE junk_table
  (
    short_field CHAR(10),
    long_field  CHAR(100),
    junk_number NUMBER
  );

    [download]

    Since my queries worked correctly, I am going to make some assumptions (I know, bad *smiles*) and offer some thing which might help.

    First off, it looks like you have RaiseError => 1 (True) in your DBI->connect. This is not a bad thing, but causes your program to terminate on the error; the error, I believe, is in your "while (@data = $handle->fetchrow_array())" statement (more specifically, fetchrow_array.) This raises an error because a column being returned exceeds LongReadLen and you have LongTruncOk set to 0 (False). To find out more information on these, I used perldoc DBI. (A lot to read there.)

    Anyway, increasing the size of LongReadLen will consume additional memory, but should have the benefit of actually letting your script run. *Grins*

    If you would like to post more of your code, I will gladly look at it.

    Also, I would like to thank you. I had never read a lot of the "extra" setting for DBI, and now have some new things with which to play -- like ChopBlanks.

[reply]
[d/l]
Re: DBI Oracle error LongReadLen too small
by Anonymous Monk on Nov 14, 2001 at 20:25 UTC
    Rhose, Blake,

    Thanks very much for your answers/pointers. They were most helpful! Problem solved.

    Blake--Thanks for the tip re Super Search...Question re Super Search-- I am unable to get it to find the "Nov 8" question on LongReadLen (if I put the string:
    LongReadLen
    in the "Words in title" and "Words in text" boxes)

    It only returns the thread on the question I asked. Have you been able to get it to work? and if so, how?

    Thanks again!!

[reply]
      Thats because 'LongReadLen' isn't actually in the title... If you only look for it in the text, that node should show up.

      -Blake

[reply]

Back to Seekers of Perl Wisdom