Connecting to an Oracle Database is more of an advanced usage of perl and is not usually needed for every script. However when you start to develop interactive web applications with perl having a backend database is fundamental. This guide will teach you how to Connect and Query Oracle Database using Perl.
In this example We will use two perl modules, DBI and XML::Simple. DBI, included with the oracle client for linux, will be used to connect to the Oracle Database. We will use XML::Simple to store the variables needed to connect, as well as the actual SQL queries we will run.
First lets declare the header, and some modules to use, and some basic environment variables.
vi /opt/dbConnect/script.pl
To start enter the following:
#!/usr/bin/perl
use XML::Simple;
use DBI;
my $dir = "/opt/dbConnect/";
my $libDir = "$dir/lib";
my $configFile = "$libDir/dbConfig.xml";
my $sqlFile = "$libDir/query.xml";
In the above snippet of code we point to two xml files, dbConfig.xml and query.xml. You could probably make them one file, but for this how-to we will keep them separate as to not confuse anything. We also declare our working directory as /opt/dbConnect/ you can change this to where your script will live. Inside /opt/dbConnect/ we have another directory called lib that will store the XML files. Now we will create the directory and xml files.
cd /opt/dbConnect/
mkdir lib
vi lib/dbConfig.xml
In the XML file put something like this, edited to your specific Database setup. For this example the password is hashed, base64. You should really use something better but this will get the job done.
Now for the next xml file we will put in our query.
vi lib/query.xml
select *
from
TABLENAME
OK so now that we have our DB XML file setup, lets go back to our script file. The next piece we need to add is setting up our DB connection and running the query.
## Connect to the DB
$ENV{'ORACLE_HOME'} = $cfg->{oracle}{home};
my $dbh = DBI->connect("dbi:Oracle:$cfg->{sql}{server}",
$cfg->{sql}{user}, decode_base64($cfg->{sql}{pass}),
{ RaiseError => 0, AutoCommit => 0 }
) or die "Cannot connect $DBI::errstrn";
$dbh->do(q{alter session set nls_territory=america});
## Run Query
my $sth = $dbh->prepare($sql->{q}{query}) or print "error = $dbh->errstrn"; ## prepare search
$sth->execute() or print "error = $dbh->errstr";
while (@_ = $sth->fetchrow_array()){ print "$_n";}
## Disconnect when done!
$dbh->disconnect;
If everything is working right you should now see the records in the table in your query print to the screen when you run the script. You can build on it from here, and instead of printing to the console make it do something else, like perhaps update records.
Hope this helps you in connecting to an Oracle Database using Perl.
Leave a Reply