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

[gopalr]

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,

[runrig]

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

[gopalr]

The version is 1.15

[runrig]

I would try the latest version, or first try the code I posted above (though I have no idea if either will work).

[kennethk]

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.

[gopalr]

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

[kennethk]

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

[MidLifeXis]

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.

[keszler]

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)?