I've actually already tried it with and without them, and I have completely removed them now. The original code I wrote when I started down this adventure didn't have them, I added them later to try and fix the issues I was seeing. The official DBI docs recommend against them unless you know you are only retrieving a part of the data set you queried.

Another of my Oracle DBAs was actually very helpful and switched me to a different service and tweaked some other options in my connection string a bit. Now it's a lot more stable, albeit seemingly a bit slower for both connect/disconnect and data retrieval. I'm still doing the silly repeated disconnect/reconnect with some long waits, but now the worst I've seen is the occasional error thrown that says:

DBD::Oracle::db disconnect failed: ORA-03113: end-of-file on communica
+tion channel
[download]

But it's rare, and I can probably just write some error handling/retry x times code around it and make it reliable enough for my purposes. Once again, and mostly just for posterity's sake (and my own, but hey, someone else may find it useful), I'll provide the full script, SQL, and sanitized tnsnames.ora file as I currently have them set since it seems to be mostly working (famous last words, probably regret them shortly) now.

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 = '%'; # filter for WHERE LIKE statement in sql for "r
+db_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, PrintErro
+r => 0, RaiseError => 1});
my $dbh = DBI->connect_cached(@connection) or die;

# check that data will be returned
my $tables_count = $dbh->selectrow_hashref('SELECT COUNT(*) as count F
+ROM rdb_main.dk_lov_header_rec WHERE note LIKE ? AND global_section_n
+ame LIKE ?', undef, $note_filter, $name_filter);
print Dumper($tables_count);
die "\nNO TABLE NUMBERS FOUND\n" if (!$tables_count->{'COUNT'});

# execute "lov_table_lookup.sql"
$dbh->disconnect or warn "Disconnection failed: $DBI::errstr\n";
sleep(5);
$dbh = DBI->connect_cached(@connection) or die;
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";

# 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"
$dbh->disconnect or warn "Disconnection failed: $DBI::errstr\n";
sleep(5);
$dbh = DBI->connect_cached(@connection) or die;
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";

$dbh->disconnect or warn "Disconnection failed: $DBI::errstr\n";
sleep(5);
[download]

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

-- List all the codes
SELECT table_number
     , lov_integer_key
     , TRIM(CAST(lov_display_key as CHAR(40))) as lov_display_key
     , TRIM(CAST(note as CHAR(80))) as note
  FROM rdb_main.dk_lov_detail_rec
 WHERE table_number IN (LIST_OF_LOV_TABLE_NUMBERS:?)
 ORDER BY table_number, note
[download]

DWAPRD =
(DESCRIPTION_LIST=
                (LOAD_BALANCE=off)
                (FAILOVER=on)
                (DESCRIPTION=
                                (ADDRESS_LIST=
                                (LOAD_BALANCE=on)
                                (ADDRESS = (PROTOCOL=TCP)(HOST=XXX)(PO
+RT = XXX)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = XXX))
                )
                (DESCRIPTION=
                                (ADDRESS_LIST=
                                (LOAD_BALANCE=on)
                                (ADDRESS = (PROTOCOL=TCP)(HOST=XXX)(PO
+RT = XXX)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = XXX))
                )
)
[download]

I am curious as to why you are using DBI->connect_cached instead of just DBI->connect?

Why this?:

$dbh->disconnect or warn "Disconnection failed: $DBI::errstr\n";
sleep(5);
$dbh = DBI->connect_cached(@connection) or die;
[download]

I read the following:

connect_cached is like connect, except that the database handle returned is also stored in a hash associated with the given parameters. If another call is made to connect_cached with the same parameter values, then the corresponding cached $dbh will be returned if it is still valid. The cached database handle is replaced with a new connection if it has been disconnected or if the ping method fails.

Caching can be useful in some applications, but it can also cause problems and should be used with care. The exact behavior of this method is liable to change, so if you intend to use it in any production applications you should discuss your needs on the dbi-users mailing list.

I don't see any need to drop your db connection and then re-connect. I recommend to ditch this connect_cached method. Connect once without caching and stay connected. There could be some glitch with this connect_cached method that is screwing things up? Stuff like "but it can also cause problems and should be used with care." are red flags to me.

Oh, I'm not a big fan of how I'm doing this either. For me it's a kludge that seems to fix another problem I had with this.

So are the all the connect/disconnects, I had another different problem without them. I had also tried adding finish method calls instead with no luck.

I'm not savvy enough at this stuff to be that effective on debugging, but I know if I do go back to just a single connect that isn't cached and/or remove the explicit disconnects I still see the issues. I'm sort of relying on my Oracle DBAs at this point, and since one of them switched the service I connected to and added a few other connection options things seem far more stable than they have ever been previously.

I'm still holding out hope I can figure out what's really going on as I work with my employer's database from Perl more, but for now I'm way behind at work and I have to let this one go.

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