Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

RESOLVED - Problem Running More than Two Successive (I think) Sql SELECT statements, DBI, DBD::Oracle

by perldigious (Priest)
on Feb 06, 2020 at 18:18 UTC ( [id://11112512]=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 provide my relatively short script and the associated SQL queries in their entirety since they're inherently sanitized at this point. The baffling (to me anyway) behavior I'm seeing now, is that I can comment out the "check that data will be returned" section and the rest of the script runs without any problems. Or, I can comment out the last "execute lov_value_lookup.sql section" and the rest of the script runs without any problems. The issue occurs when I have the entire script with all three SELECT queries try to run, then it hangs on the 3rd after printing out the first 146 of 428 lines and either just freezes up forever or eventually gives an ORA-25401 error. I'm checking with my Oracle DBA team, who are probably about as sick of me at this point as you are, but I'm at a complete loss as to why this would happen. If anyone has any thoughts or suggestions, I'd appreciate it.

use utf8; use strict; use warnings; use Data::Dumper; use DBI; my $note_filter = '%'; # filter for WHERE LIKE statement in sql for "r +db_main.dk_lov_header_rec.note" my $name_filter = '%COUNTRY%'; # filter for WHERE LIKE statement in sq +l for "rdb_main.dk_lov_header_rec.global_section_name" # bland credentials open(my $access_fh, '<', "$ENV{USERPROFILE}".'\Documents\Perl\DW_acces +s.txt') or die "Cannot open \"DW_access.txt\": $!."; chomp(my $user = <$access_fh>); chomp(my $pass = <$access_fh>); close($access_fh); # read in sql statement from "lov_table_lookup.sql" my $lov_table_sql; open(my $lov_table_sql_fh, '<', 'lov_table_lookup.sql') or die "Cannot + open \"lov_table_lookup.sql\": $!."; $lov_table_sql .= $_ while(<$lov_table_sql_fh>); close($lov_table_sql_fh); # read in sql statement from "lov_value_lookup.sql" my $lov_value_sql; open(my $lov_value_sql_fh, '<', 'lov_value_lookup.sql') or die "Cannot + open \"lov_value_lookup.sql\": $!."; $lov_value_sql .= $_ while(<$lov_value_sql_fh>); close($lov_value_sql_fh); # establish database connection my $dsn = 'dbi:Oracle:DWAPRD'; my @connection = ($dsn, $user, $pass, {InactiveDestroy => 1}); my $dbh = DBI->connect_cached(@connection) or die; # check that data will be returned my $sth0 = $dbh->prepare('SELECT COUNT(*) as count FROM rdb_main.dk_lo +v_header_rec WHERE note LIKE ? AND global_section_name LIKE ?') or di +e; $sth0->execute($note_filter, $name_filter) or die; my $rows_count = $sth0->fetchrow_hashref; print Dumper($rows_count); die "\nNO TABLE NUMBERS FOUND\n" if (!$rows_count->{'COUNT'}); $sth0->finish; # execute "lov_table_lookup.sql" my $sth1 = $dbh->prepare($lov_table_sql) or die; $sth1->execute($note_filter, $name_filter) or die; # store table numbers in array print "\n----------\n"; my $print_format = '%10s %12s %-32s %-80s'; printf("$print_format\n", 'ROW_NUMBER', 'TABLE_NUMBER', 'GLOBAL_SECTIO +N_NAME', 'NOTE'); my @table_numbers; my $row_counter = 1; while(my $row = $sth1->fetchrow_hashref) { push(@table_numbers, $row->{'TABLE_NUMBER'}); foreach (keys(%$row)) {$row->{$_} = '' if (!defined($row->{$_}))}; + # change all NULLs to empty strings printf("$print_format\n", $row_counter, $row->{'TABLE_NUMBER'}, $r +ow->{'GLOBAL_SECTION_NAME'}, $row->{'NOTE'}); $row_counter++; } print "----------\n"; $sth1->finish; # adjust $lov_value_sql for variable number of bind parameters my $bind_inserts; $bind_inserts .= '?, ' foreach (@table_numbers); $bind_inserts =~ s/, $//; # discard hanging comma $lov_value_sql =~ s/LIST_OF_LOV_TABLE_NUMBERS:\?/$bind_inserts/; # execute "lov_value_lookup.sql" my $sth2 = $dbh->prepare($lov_value_sql) or die; print "\n----------\n"; $print_format = '%10s %12s %15s %-15s %-80s'; printf("$print_format\n", 'ROW_NUMBER', 'TABLE_NUMBER', 'LOV_INTEGER_K +EY', 'LOV_DISPLAY_KEY', 'NOTE'); $row_counter = 1; $sth2->execute(@table_numbers) or die; while(my $row = $sth2->fetchrow_hashref) { foreach (keys(%$row)) {$row->{$_} = '' if (!defined($row->{$_}))}; + # change all NULLs to empty strings printf("$print_format\n", $row_counter, $row->{'TABLE_NUMBER'}, $r +ow->{'LOV_INTEGER_KEY'}, $row->{'LOV_DISPLAY_KEY'}, $row->{'NOTE'}); $row_counter++; } print "----------\n"; $sth2->finish;

-- Find the table number(s) that have the codes you wish to decode 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 ? AND global_section_name LIKE ? ORDER BY global_section_name

-- List all the codes 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

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

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: Problem Running More than Two Successive (I think) Sql SELECT statements, DBI, DBD::Oracle
by NetWallah (Canon) on Feb 07, 2020 at 04:17 UTC

      I'm not sure on what type of Oracle server it is, I've been in contact with my Oracle DBA team and they aren't sure why I'm seeing issues either. Unfortunately, they usually aren't that helpful for the non-sanctioned/curated connections, and most people end up fixing things themselves or hacking workarounds. The tnsnames.ora file my DBA team recommend on their intranet page for the connection I'm using is exactly like that given in both those links. When I spoke to one of the DBAs last he had me try a different file, but it didn't work either and I still see the same issues.

      It just doesn't make any sense to me. I'm able to run the exact same queries in a SQL Developer environment making the exact same connection, and and Alation web environment (where the connection details are hidden from me). Can also connect and get data out of MicroStrategy web application, but there it's so heavily curated you can't even write your own SQL.

      I'm about ready to give up and just resign myself back to the tedium of dumping big .tsv, .csv, .xlsx, or whatever files from those sorts of environments and reading them in to Perl that way, I'm just so sick of having to do that I was hoping I could make this work. :-/

      My knowledge of this stuff is extremely limited, I'm not a programmer by profession, it's just something I do to make my regular @jobs a lot more efficient and I love Perl because it usually "just works" the way I want it to with very little cajoling necessary. This database stuff has been the first exception to that for me so far. It's killing me, because I feel like I'm so close to making this work, and I've already spent a lot of time on it on my employer's dime. Just being able to figure out how to connect and retrieve data at all was a huge win for me personally, but if I can't make it work consistently and reliably it's a moot point for my actual job productivity.

      Just another Perl hooker - My clients appreciate that I keep my code clean but my comments dirty.
        What happens if each prepare and execute run in a separate connection?

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

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://11112512]
Approved by haukex
Front-paged by haukex
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others taking refuge in the Monastery: (5)
As of 2024-03-28 13:31 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found