Archive

Posts Tagged ‘hash of hash arrays’

Perl – Oracle SQL – pass sql output into a perl variable


It is possible to run a sql command to retrieve results to an array without using the DBI or DBD modules of perl.
The following script (kr.pl) connects to sqlplus, runs a pre-written sql file to get the owner and table_name from dba_tables, puts this into an array in perl, then perl processes the array into a hash of has arrays, finally printing out a list of tables and owners. This is just an example of how perl can be used along with sqlplus to retrieve data from the database. We could take the perl script further and manipulate the data within the hash of hash array, but that is beyond the scope of this post.

#!/usr/bin/perl
@test = `sqlplus -S \/ as sysdba \@kr.sql`;
my $count = 0;
foreach my $value (@test){
my ($table_name, $owner) = split(/#/,$value);
$count++;
if (! defined %testarray) {
%testarray = ($count => {table_name=> $table_name, owner=> $owner,},);
}
else {
@testarray{$count} = {'table_name' => $table_name, 'owner' => $owner};
}
}
foreach my $table (keys %testarray) {
for my $number (keys %{ $testarray{$table}}) {
print "$number=$testarray{$table}{$number}";
}
print "\n ";
}

The sql code (kr.sql) is as follows:

set feedback off pages 0 timing off echo off
set feedback off pages 0 timing off echo off
set feedback off pages 0 timing off echo off
select table_name||'#'|| owner from dba_tables where owner = 'SYS' and table_name like '%JAVA%';
exit
~

The output of the perl script is as follows:

/home/krobbe > ./kr.pl
owner=SYS
table_name=PROCEDUREJAVA$
owner=SYS
table_name=JAVA$RMJVM$AUX2
owner=SYS
table_name=JAVA$JVM$STEPS$DONE
owner=SYS
table_name=JAVA$JVM$STATUS
owner=SYS
table_name=WRH$_JAVA_POOL_ADVICE
owner=SYS
table_name=TRIGGERJAVAF$
owner=SYS
table_name=JAVA$RMJVM$AUX
owner=SYS
table_name=JAVA$POLICY$
owner=SYS
table_name=JAVAOBJ$
owner=SYS
table_name=TRIGGERJAVAC$
owner=SYS
table_name=TRIGGERJAVAM$
owner=SYS
table_name=TRIGGERJAVAS$
owner=SYS
table_name=JAVA$RMJVM$AUX3
owner=SYS
table_name=JAVA$POLICY$SHARED$TABLE
owner=SYS
table_name=JAVASNM$
owner=SYS
table_name=JAVA$PREFS$

Follow

Get every new post delivered to your Inbox.

Join 27 other followers