If you poke your head into $ORACLE_HOME you'll notice a PERL directory. If you dig around you'll find that you've got a pretty decent PERL setup at your fingertips. In 10g (10.1.0) you've got PERL 5.6.1, plus a wide variety of applicable modules including: mod_perl, URI, Apache, LWP, RPC, and more. Of possibly more interest are the inclusion of the OraPERL module and DBI with the DBD for Oracle! Proof that Oracle doesn't hate UNIX admins. A quick look at CPAN, however, will tell you that the OraPERL module is intended only for compatability with PERL 4 apps and "any new development should use DBI directly."
The included version of the Oracle DBD is 1.12, in Oracle 10.1.0. Its increadably easy to use and most SAs will probly find the DBD interface far more "homey" than PL/SQL.
To use the Oracle distribution of PERL you'll need to modify 2 enviromental variables. Firstly you'll need to add the Oracle library directory to LD_LIBRARY_PATH if you don't have it included in the run time linkers configuration (ldconfig in Linux, crle in Solaris). Secondly you'll need to put the PERL module directories in your PERL5LIB variable so that they are included in INC. If you fail to add these your likely to get a slew of errors.
$ export LD_LIBRARY_PATH=/u01/app/oracle/product/10.1.0/db_1/lib32 $ export PERL5LIB=/u01/app/oracle/product/10.1.0/db_1/perl/lib/site_\ perl/5.6.1/sun4-solaris/:/u01/app/oracle/product/10.1.0/db_1/perl/lib/5.6.1
If you do, however, forget to set LD_LIBRARY_PATH you'll notice interestingly that the Oracle DBD uses the OCI.
Once you've got things setup, you can use PERL and the DBI like you'd expect. If your new to the DBI I'd strongly suggest picking up the excellent book Programming the PERL DBI from O'Reilly. (Insidently, Tim Bunce who co-wrote Programming the PERL DBI is also the author of the Oracle DBI.)
Here's a simple example of using the PERL DBI provided with Oracle10g:
#!/u01/app/oracle/product/10.1.0/db_1/perl/bin/perl
# Example PERL DBI/DBD Oracle Example on Oracle 10g
use DBI;
my $dbname = "testdb"; ## DB Name from tnsnames.ora
my $user = "ben";
my $passwd = "passwd";
#### Connect to the database and return a database handle
$dbh = DBI->connect("dbi:Oracle:${dbname}", $user, $passwd);
if($dbh){
print("Connected as user $user\n");
} else {
print("Failed to connect!\n");
exit;
}
#### Prepare and Execute a SQL Statement Handle
my $sth = $dbh->prepare("SELECT owner,table_name,num_rows FROM all_tables");
$sth->execute();
print("All tables - Got rows:\n");
print("Owner\tTableName\tNumRows\n");
print("-----\t---------\t-------\n");
while(@row = $sth->fetchrow_array()){
print("$row[0]\t$row[1]\t$row[2]\n");
}
print("Select Done!...");
#### Disconnect
if($dbh->disconnect){
print("Disconnected\n");
} else {
print("Failed to disconnect\n");
}
In the above script we're grabbing 3 columns from the Data Dictionary's ALL_TABLES system table. We connect, grab the rows and output them, and then disconnect from the database when we're done.
If you have trouble connecting to the database, remember that you need to connect through the listener (you can connect locally, but it's pretty figity) and ensure that you can properly tnsping the database before freaking out about your script.
The output looks like this (several rows removed for clarity):
bash-2.05$ ./example.pl Connected as user ben All tables - Got rows: Owner TableName NumRows ----- --------- ------- SYS DUAL 1 SYS SYSTEM_PRIVILEGE_MAP 173 SYS TABLE_PRIVILEGE_MAP 23 ... SYS PLAN_TABLE$ SYS OLAPTABLEVELS Select Done!...Disconnected bash-2.05$
For more information on usign the DBI please refer to CPAN and/or Programming the PERL DBI.
http://search.cpan.org/timb/DBI-1.45/DBI.pm
http://search.cpan.org/timb/DBD-Oracle-1.15/Oracle.pm