Perl and Oracle

Many people who are working with Oracle might want to have the possibility to connect to the database with Perl also many Perl coders might want to have the possibility to use an oracle database.

To be able to use Perl in combination with an Oracle database you need, besides Perl and an Oracle database, Perl DBI and DBD Oracle.

DBI is the DBI is a database access module for the Perl programming language. It defines a set of methods, variables, and conventions that provide a consistent database interface, independent of the actual database being used. It is important to remember that the DBI is just an interface. The DBI is a layer of "glue" between an application and one or more database driver modules. It is the driver modules which do most of the real work. The DBI provides a standard interface and framework for the drivers to operate within.

DBD Oracle is a Perl module which works with the DBI module to provide access to Oracle databases.

To install DBI and DBD Oracle there are 2 ways to dot this. You can use a 'perl -MCPAN -e' like way or you can download the source and build from scratch. We will use the "build from scratch" methode.

First we install DBI:

1) Make sure you are root!
2) Download DBI from CPAN
3) Use gunzip and tar to extract the archive:
gunzip DBI-1.48.tar.gz
tar -xvf DBI-1.48.tar
4) Make and install DBI:
perl Makefile.PL
make
make install
5) Cleanup all the downloaded stuff and the extracted directories.

Secondly we will build DBD Oracle:

1) Make sure you are root!
2) Download DBI Oracle from CPAN
3) Use gunzip and tar to extract the archive:
gunzip DBD-Oracle-1.16.tar.gz
tar -xvf DBD-Oracle-1.16.tar
4) Make and install DBD Oracle:
perl Makefile.PL
make
make install
5) Cleanup all the downloaded stuff and the extracted directories.

Now you should have a working Perl database interface and working oracle drivers. This means we can run a test. You could for example use this perl script

#------------------------------------------------------------------

use strict;
use DBI;
 
my $dbh = DBI->connect( 'dbi:Oracle:orcl',
                        'jeffrey',
                        'jeffspassword',
                        {
                          RaiseError => 1,
                          AutoCommit => 0
                        }
                      ) || die "Database connection not made: $DBI::errstr";

 
my $sql = qq{ SELECT id, name, title, phone FROM employees };
my $sth = $dbh->prepare( $sql );
$sth->execute();

 
my( $id, $name, $title, $phone );
$sth->bind_columns( undef, \$id, \$name, \$title, \$phone );

 
while( $sth->fetch() ) {
  print "$name, $title, $phone\n";
}

 
$sth->finish();
$dbh->disconnect();
#------------------------------------------------------------------

A script origanly done by Jeffrey William Baker, on his website there are some more details about the example.