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]