Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

RESOLVED - DBD::Oracle::st fetchrow_hashref failed: ORA-25401

by perldigious (Priest)
on Jan 29, 2020 at 20:22 UTC ( #11112044=perlquestion: print w/replies, xml ) Need Help??

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

Hi, Monks. I'm going to start by explaining my original problem (as this node is titled) to make sure I'm not doing the XY thing. This is my second day ever messing around with the Perl DBI, so I admit to all ignorance in advance and as always that I'm no power user of Perl in general.

I started getting the title error when attempting to retrieve some data from my employer's Oracle database. It seems to consistently hang on one of two rows as it's fetching them (it successfully retrieves many), then throws the error code. I think I can safely say the issue is with the script I've written, or not having set something up correctly, since I'm able to run the same query just fine in an SQL Developer environment using the same connection string. The code snip it's hanging on is pretty simple...

while(my $row = $sth->fetchrow_hashref()) { print $row->{'TABLE_NUMBER'}, " ", $row->{'LOV_INTEGER_KEY'}, +" ", $row->{'LOV_DISPLAY_KEY'}, " ", $row->{'NOTE'}, "\n"; }

I found this node of nearly the same title and attempted to follow the basic advice of updating my DBD::Oracle module install, but when I try that seems to be having a problem executing on my machine. Here's a screen dump of what's happening, not sure what I need to do here.

Configuring M/MJ/MJEVANS/DBD-Oracle-1.80.tar.gz with Makefile.PL Using DBI 1.642 (for perl 5.028001 on MSWin32-x64-multi-thread) instal +led in C:/Strawberry/perl/vendor/lib/auto/DBI/ Configuring DBD::Oracle for perl 5.028001 on MSWin32 (MSWin32-x64-mult +i-thread) If you encounter any problem, a collection of troubleshooting guides are available under lib/DBD/Oracle/Troubleshooting. 'DBD::Oracle::Troubleshooting' is the general troubleshooting guide, while platform-specific troubleshooting hints live in their labelled sub-document (e.g., Win32 hints are gathered in 'lib/DBD/Oracle/Troubleshooting/Win32.pod'). Installing on a MSWin32, Ver#10.0 Using Oracle in C:/Oracle/client/product/12.1.0/client_1 DEFINE _SQLPLUS_RELEASE = "1201000200" (CHAR) Oracle Version 12.1.0.2 (12.1) Found oci directory Using OCI directory 'oci' dlltool: unrecognized option `--input-def' Usage dlltool <options> <object-files> --machine <machine> --output-exp <outname> Generate export file. --output-lib <outname> Generate input library. --add-indirect Add dll indirects to export file. --dllname <name> Name of input dll to put into output lib. --def <deffile> Name input .def file --output-def <deffile> Name output .def file --base-file <basefile> Read linker generated base file --no-idata4 Don't generate idata$4 section --no-idata5 Don't generate idata$5 section -v Verbose -U Add underscores to .lib -k Kill @<n> from exported names --as <name> Use <name> for assembler --nodelete Keep temp files. Could not find or create liboci.a. Warning: No success on command[C:\Strawberry\perl\bin\perl.exe Makefil +e.PL] MJEVANS/DBD-Oracle-1.80.tar.gz C:\Strawberry\perl\bin\perl.exe Makefile.PL -- NOT OK

Any help any Monk can provide is much appreciated, at this point I'm running down random Google search holes that all seem to lead nowhere.

----------

UPDATE: For posterity's sake (and mine if I ever have to refer back)

Unfortunately I don't have a "root cause" as to why I was seeing this error, but I do at least seem to have a fix for it. When I added some CAST and TRIM functions around the the more exotic data fields all the problems went away. With just CAST they mostly went away, but it would still freeze up on occasion. The intermittent nature of the failures was/is the most disconcerting thing about them... inconsistency when running the same script with the same SQL was very vexing to me. I'm not even certain where exactly the problem was (The database? DBI? DBD::Oracle? My terminal?).

SELECT table_number , TRIM(CAST(global_section_name as CHAR(32))) as global_section_n +ame , TRIM(CAST(note as CHAR(80))) as note FROM rdb_main.dk_lov_header_rec WHERE note LIKE ? OR global_section_name LIKE ? ORDER BY global_section_name

SELECT table_number , lov_integer_key , TRIM(CAST(lov_display_key as CHAR(2))) as lov_display_key , TRIM(CAST(note as CHAR(30))) as note FROM rdb_main.dk_lov_detail_rec WHERE table_number IN (LIST_OF_LOV_TABLE_NUMBERS:?) ORDER BY table_number, note

I had gotten a bit sidetracked with a different issue that I wanted to resolve first since it was slowing down my debugging terribly.

Thank you to all the esteemed Monks who helped me get this figured out, it's very much appreciated:
choroba for his help getting my DBD::Oracle module updated when the install was failing for me.
pryrt for teaching me a quick tip to help me debug SYSTEM PATH issues.
marto for his tracing suggestion, perhaps someday I'll be less clueless and it will be useful to me :P.
TieUpYourCamel for leading me down the ultimately winning path of doing a little manual data massaging.

Now I'm off to pick more fights with the Perl DBI where I muddle the line between winning and losing. :-)

-------------------------------------------------

UPDATE - Resolved: See update at bottom of post here.

Just another Perl hooker - My clients appreciate that I keep my code clean but my comments dirty.

Replies are listed 'Best First'.
Re: DBD::Oracle::st fetchrow_hashref failed: ORA-25401
by choroba (Archbishop) on Jan 29, 2020 at 22:07 UTC
    These two lines seem related:
    dlltool: unrecognized option `--input-def' --def <deffile> Name input .def file
    So, I would replace --input-def by --def on line 330 of Makefile.PL and rerun the installation.

    map{substr$_->[0],$_->[1]||0,1}[\*||{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^*ARGV,3]

      o, I would replace --input-def by --def on line 330 of Makefile.PL and rerun the installation.

      I would not. I would fix %PATH% to exclude some 50 year old version of dlltool. the dlltool.exe that comes with strawberryperl accepts --input-def option.

        How can I update my system PATH to do this? I already have "C:\Strawberry\perl\bin" at the top. Doesn't that point it there first?

        Just another Perl hooker - My clients appreciate that I keep my code clean but my comments dirty.

      Generally stuff like this is a bit outside my depth, but I'll try to follow along. Apologies if I have to ask you to explain things to me like I'm a five year old.

      It's odd, searching for the proper Makefile.PL to edit and going in to "C:\Strawberry\cpan\build" of my install I show 12 different "DBD-Oracle-1.80" directories just progressively numbered all last modified a couple days ago when I was trying to get that module installed and working for me properly.

      I tried deleting them all but the original "DBD-Oracle-1.80-0" one and updating the Makefile in it as you suggested, but it didn't seem to help. I also tried deleting them all and reinstalling form CPAN but got the same result.

      I did have to mess around with my system PATH variable after installing a suitable version of Oracle Client, I mostly followed afoken's guide here. Currently my system PATH variable has "C:\Strawberry\Perl\bin" at the top followed by "%ORACLE_HOME%\bin". If I recall, one issue I had is that when those were flipped my system kept loading an earlier version of Perl out of the Oracle directory instead of the Strawberry\Perl directory.

      Just another Perl hooker - My clients appreciate that I keep my code clean but my comments dirty.
        > cpan ... cpan[1]> get DBD::Oracle ... cpan[2]> look DBD::Oracle ... # Fix the Makefile.PL > perl Makefile.PL > make # or your variant whereof > make install > exit cpan[3]>

        map{substr$_->[0],$_->[1]||0,1}[\*||{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^*ARGV,3]
Re: DBD::Oracle::st fetchrow_hashref failed: ORA-25401
by marto (Cardinal) on Jan 30, 2020 at 15:19 UTC

      I had found that same first link as well, but when I compared my tnsnames.ora file to it it's identical to the "after" version (obviously with the correct host, port, and service name for my employer's Oracle DB). Very simple, it's actually what my Oracle Database Admins have posted to use as the default on their intranet page at my employer for these things. That same tnsnames.ora file is what I loaded in to my SQL Developer install to get it working, and there I can run the same query that my Perl script chokes on.

      DWAPRD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = XXX)(PORT = XXX)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XXX) ) )

      I'm only just learning this stuff, all my previous database access has been heavily curated through various approved environments that handle all the connection stuff behind the scenes. The only reason I want direct access is so that after 2-3 years of dumping tab delimited and/or Excel files via those approved environments and reading them in to my scripts, I can skip all that manual overhead. Sometimes it's horribly tedious because I have to dump multiple files from multiple sources, save them in different formats, then get them in to a directory with my Perl scripts to do what I need... man, it would be so nice to skip all that with a direct pipeline to the various data sources via Perl. So here I am, struggling. :-/

      I'll see if I can figure anything out with the tracing suggestion.

      Just another Perl hooker - My clients appreciate that I keep my code clean but my comments dirty.
Re: DBD::Oracle::st fetchrow_hashref failed: ORA-25401
by TieUpYourCamel (Scribe) on Jan 30, 2020 at 16:02 UTC
    What happens if you do this?
    my $results = $sth->fetchall_arrayref( {} ); foreach my $row (@$results) { print $row->{'TABLE_NUMBER'}, " ", $row->{'LOV_INTEGER_KEY'}, " ", $row->{'LOV_DISPLAY_KEY'}, " ", $row->{'NOTE'}, "\n"; }

      In this case, it just seems to hang forever once it reaches this point in the execution. The script runs an earlier query just fine. I'm thinking it's something funky in the data that something in my Perl specific data pipeline is choking on. There are some funky things in the data of these particular tables, especially the "NOTE" data which I only read to strip a country name from the front of and throw away a bunch of junk on the end. Example of what I mean:

      47 138 0 1O ARMENIA + AMFFFTTFTFXXXX XXXXX XX O X X TTFFFARM 48 138 0 1T ARUBA + AWFFFOFTTTXX X XXXXX X X O X TTFTTABW 49 138 0 1Y AUSTRALIA + AUFFFTFTTTX X XX OXXXXX XFFFFFAUS 50 138 0 23 AUSTRIA + ATFFFTTTTTX X XX X X OXXXXX XFFFTTAUT

      Sometimes I get in over my head on stuff like this, especially when I'm largely ignorant about how to properly configure my tools to do what I think I might need or want.

      Just another Perl hooker - My clients appreciate that I keep my code clean but my comments dirty.
        Try removing columns from the query to see if one of them is causing the error. What are the data types of the columns?

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (7)
As of 2020-10-26 13:05 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My favourite web site is:












    Results (251 votes). Check out past polls.

    Notices?