Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

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

by TieUpYourCamel (Scribe)
on Jan 30, 2020 at 16:02 UTC ( #11112103=note: print w/replies, xml ) Need Help??


in reply to RESOLVED - DBD::Oracle::st fetchrow_hashref failed: ORA-25401

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

Replies are listed 'Best First'.
Re^2: DBD::Oracle::st fetchrow_hashref failed: ORA-25401
by perldigious (Priest) on Jan 30, 2020 at 16:34 UTC

    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?

        It runs fine when I leave out "NOTE" and "LOV_DISPLAY_KEY" which are VARCHAR2. The others are just NUMBER type.

        I'm guessing the issue is with the "NOTE" data. If I leave out just "NOTE", it runs and gets all the rows, but then hangs when it attempts to execute the sql again for a second time through a loop it's inside... I'm guessing that's an issue with my code and trying to do it in a loop that way, this is my first time attempting stuff like this so I'm still learning. A broader snip of my perl script:

        # establish database connection my $dsn = "dbi:Oracle:DWAPRD"; my $dbh = DBI->connect($dsn, $user, $pass); # execute "lov_table_lookup.sql" my $sth = $dbh->prepare($lov_table_sql); $sth->execute($note_filter, $name_filter); # print returned data and store table numbers in array my @table_numbers; print "\n----------\n"; while(my $row = $sth->fetchrow_hashref()) { print $row->{'TABLE_NUMBER'}, " ", $row->{'GLOBAL_SECTION_NAME'}, +" ", $row->{'NOTE'}, "\n"; push(@table_numbers, $row->{'TABLE_NUMBER'}); } print "----------\n"; # retrieve and print lov value list for each table number found foreach(@table_numbers) { my $row_counter = 1; # execute "lov_value_lookup.sql" my $sth = $dbh->prepare($lov_value_sql); $sth->execute($_); print "\n----------\n"; while(my $row = $sth->fetchrow_hashref()) { print "$row_counter ", $row->{'TABLE_NUMBER'}, " ", $row->{'LO +V_INTEGER_KEY'}, " ", $row->{'LOV_DISPLAY_KEY'}, " ", #$row->{'NOTE'}, "\n"; $row_counter++; } print "----------\n"; }

        And the error message I get in that case, which seems to be just telling me "hey dummy, you can't do that with a loop that way" or something of that nature:

        DBD::Oracle::db prepare failed: ORA-25408: can not safely replay call +(DBD ERROR: OCIStmtExecute/Describe) [for Statement "-- List all the +codes SELECT table_number , lov_integer_key , lov_display_key --, note FROM rdb_main.dk_lov_detail_rec WHERE table_number = ? ORDER BY table_number, lov_integer_key "] at lov_lookup.pl line 51. Can't call method "execute" on an undefined value at lov_lookup.pl lin +e 52.

        EDIT: Yes, it runs fine when I moved the prepare statement outside the loop (derp on my part), so the problem is presumably in the "NOTE" column data for those particular rows it's hanging on.

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

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (2)
As of 2020-07-04 22:34 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?