Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
PerlMonks  

DBD::Oracle::st fetchrow_hashref failed: ORA-24345

by gopalr (Priest)
on Sep 06, 2013 at 16:25 UTC ( [id://1052731] : perlquestion . print w/replies, xml ) Need Help??

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

Hi

I am getting the following error while running fetchrow_hashref below:

$sql='SELECT a.serial_num user_serial FROM device a WHERE (LOWER(a.nam +e) = ? OR a.mgmt_ip_addr = ?)'; $cmd = $dbh->prepare($sql); $cmd->execute('test','test'); while ($row = $cmd->fetchrow_hashref()) { ..... }
Error
DBD::Oracle::st fetchrow_hashref failed: ORA-24345: A Truncation or nu +ll fetch error occurred (DBD ERROR: ORA-01406 error on field 1 of 1, +ora_type 1) [for Stat ement " SELECT a.serial_num user_serial FROM device a WHERE (LOWER(a.name) = ? OR a.mgmt_ip_addr = ?) " with ParamValues: :p1='test', :p2='test'] at data.cgi line 720.

NOTE:The column SERIAL_NUM has the type VARCHAR2(80) and it has many special characters

Please help me in this regard

Thanks,

Replies are listed 'Best First'.
Re: DBD::Oracle::st fetchrow_hashref failed: ORA-24345
by runrig (Abbot) on Sep 06, 2013 at 16:38 UTC

    What version of DBD::Oracle? There seems to be an old patch for this issue here, and the latest version of the code seems to cover that issue (though the code is a bit different), so I'm wondering if you have a (very) old version.

    And just curious if the following would fix the problem:

    $sth->execute(...); my $serial_num = '0' x 50_000; # Or more? $sth->bind_col(1, \$serial_num); while ($sth->fetch()) { print "$serial_num\n"; }

      The version is 1.15

        I would try the latest version, or first try the code I posted above (though I have no idea if either will work).
Re: DBD::Oracle::st fetchrow_hashref failed: ORA-24345
by kennethk (Abbot) on Sep 06, 2013 at 16:48 UTC
    1. Please update your post to include an open <code>, as you've gotten mangled.
    2. Are you certain the field is VARCHAR2(80)? Not to second guess you, but every time I've seen that error, it was associated with a LOB.
    3. What do you mean by 'special characters'? Non-alphanumerics? Non-ASCII? If you are dealing with high-bit characters, can you show us your connector? Have you modified $ENV{NLS_LANG}?

    It looks like there's a message on the perl.dbi.users mailing list that might be relevant. It suggests the problem is UTF related, and we need to debug your connector.


    #11929 First ask yourself `How would I do this without a computer?' Then have the computer do it the same way.

      Hi

      These are the special characters:

      ^$!@&*_+-={}|\;:''>,./?~`+    ~_+{}|>?~_+|>?`-=\,.---,03,83,,-,~_

      I have resolved this issue by setting up the environment variable to '.WE8ISO8859P1' from .AL32UTF8

      $ENV{'NLS_LANG'} = '.WE8ISO8859P1'

      Now it works fine.. Thanks for your guidance

        Glad to have provided guidance, and thank you for posting your solution in case another knowledge seeker runs into a similar problem.


        #11929 First ask yourself `How would I do this without a computer?' Then have the computer do it the same way.

Re: DBD::Oracle::st fetchrow_hashref failed: ORA-24345
by MidLifeXis (Monsignor) on Sep 06, 2013 at 16:46 UTC

    For info on the errors:

    $ oerr ORA 01406 01406, 00000, "fetched column value was truncated" // *Cause: // *Action: $ oerr ORA 24345 24345, 00000, "A Truncation or null fetch error occurred" // *Cause: A truncation or a null fetch error" // *Action: Please ensure that the buffer size is long enough to // store the returned data.

    --MidLifeXis

Re: DBD::Oracle::st fetchrow_hashref failed: ORA-24345
by keszler (Priest) on Sep 06, 2013 at 16:50 UTC
    What do you get when running the query

    SELECT a.serial_num user_serial FROM device a WHERE (LOWER(a.name) = 'test' OR a.mgmt_ip_addr = 'test')

    in SQLPlus (or any other tool)?