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())
{
    .....

}

[download]
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.

[download]

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";
}

    [download]
[reply]
[d/l]

      The version is 1.15

[reply]
        I would try the latest version, or first try the code I posted above (though I have no idea if either will work).
[reply]
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.

[reply]
[d/l]
[select]

      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

[reply]
[d/l]
[select]

        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.

[reply]
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.

    [download]

    --MidLifeXis

[reply]
[d/l]
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)?

[reply]
[d/l]

Back to Seekers of Perl Wisdom