Perl

Larry Wall, creator of the Perl language

Perl is a powerful scripting language that supports Oracle integration and is available on almost all operating systems. It is also included with the Oracle 10g and Oracle 11g database server.

Definition from the Perl documentation:

Perl is a high-level programming language with an eclectic heritage written by Larry Wall and a cast of thousands. It derives from the ubiquitous C programming language and to a lesser extent from sed, awk, the Unix shell, and at least a dozen other tools and languages. Perl's process, file, and text manipulation facilities make it particularly well-suited for tasks involving quick prototyping, system utilities, software tools, system management tasks, database access, graphical programming, networking, and world wide web programming. These strengths make it especially popular with system administrators and CGI script authors, but mathematicians, geneticists, journalists, and even managers also use Perl. Maybe you should, too.

History

Oraperl was a Perl 4 binary (with Oracle OCI calls compiled in) used to access Oracle from Perl programs. OraPerl was superseded by the more generic DBI database driver in Perl 5, which uses DBD::Oracle to access Oracle.

For compatibility DBD::Oracle ships with an OraPerl emulation layer. This emulation layer can run legacy oraperl scripts without modification by translating the oraperl API calls into the corresponding DBI calls. However, the preferred method for database access is DBI.

Perl and Oracle

You need two CPAN modules to enable Oracle connectivity: The first is the main DBI (DB Interface) module, the other is a DBD (DB Driver) module. Several DBD modules are available - to support the different database systems, including Oracle and MySQL. The Oracle module is called DBD::Oracle.

• DBI (previously called DBperl) is a database independent interface module for Perl. It defines a set of methods, variables and conventions that provide a consistent database interface independent of the actual database being used.

• DBD::Oracle is the Oracle specific module for DBI. It can be downloaded from CPAN (download now).

Perl Code

Setting the Oracle environment

Here is an example of how to properly set required Oracle environment variables in Perl:

my $ORACLE_HOME = "/app/oracle/product/11.2.0/db_1";
my $ORACLE_SID="orcl";

$ENV{ORACLE_HOME}=$ORACLE_HOME;
$ENV{ORACLE_SID}=$ORACLE_SID;
$ENV{PATH}="$ORACLE_HOME/bin";
# $ENV{LD_LIBRARY_PATH}="$ORACLE_HOME/lib";

CONNECT to Oracle with DBD::Oracle

Look at these example code pieces. In this example, ORCL is the database connect descriptor.

use strict;
use DBI;
my $dbh = DBI->connect( 'dbi:Oracle:orcl',
                        'scott',
                        'tiger',
                      ) || die "Database connection not made: $DBI::errstr";
$dbh->disconnect;

... or with additional connect options:

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

This example connects to two databases simultaneously:

my $dbh1 = DBI->connect( "dbi:Oracle:ORCL1", "scott", "tiger" )
   or die "Can't connect to 1st Oracle database: $DBI::errstr\n";
my $dbh2 = DBI->connect( "dbi:Oracle:ORCL2", "scott", "tiger" )
   or die "Can't connect to 2nd Oracle database: $DBI::errstr\n";
$dbh1->disconnect or warn "DB1 disconnection failed: $DBI::errstr\n";
$dbh2->disconnect or warn "DB2 disconnection failed: $DBI::errstr\n";

SELECT data from Oracle with DBD::Oracle

Look at this example:

my $sql = qq{ SELECT TNAME, TABTYPE FROM TAB };    # Prepare and execute SELECT
my $sth = $dbh->prepare($sql);
$sth->execute();

my($tname, $tabtype);                     # Declare columns
$sth->bind_columns(undef, \$tname, \$tabtype);

print "List of tables:\n\n";              # Fetch rows from DB
while( $sth->fetch() ) {
    print "Object: $tname, type: $tabtype\n";
}
$sth->finish();                           # Close cursor

Invoke stored procedures with DBI

There is currently no standard way to call stored procedures with DBI. However, if you have created a stored procedure within an Oracle database, you can use $dbh->do() to immediately execute the procedure:

$dbh->do("BEGIN myPackage.myProcedure; END;");

DBD::Oracle and NULL values

NULL values are treated as the value "undef" in DBI. Use one of the following methods to insert NULLs into an Oracle database:

$rc = $dbh->do("INSERT INTO tableName VALUES(SYSDATE, NULL)");

or...

$sth = $dbh->prepare(qq{ INSERT INTO tableName VALUES (?, ?) });
$sth->execute("10-JUN-2005", undef);

When queried, NULLs should be tested against undef. Example:

if (!defined $var) { ...

Executing the Perl supplied with Oracle

Here is an example of how to execute the Perl installed with Oracle, under Unix. One reason for doing this is to access DBD::Oracle where this is not otherwise available. This technique assumes that ORACLE_HOME and ORACLE_SID have been set.

#!/usr/bin/perl

# Run under Oracle Perl, for DBI.

BEGIN {
    die "ORACLE_HOME not set\n" unless $ENV{ORACLE_HOME};
    unless ($ENV{OrAcLePeRl}) {
       $ENV{OrAcLePeRl} = "$ENV{ORACLE_HOME}/perl";
       $ENV{PERL5LIB} = "$ENV{PERL5LIB}:$ENV{OrAcLePeRl}/lib:$ENV{OrAcLePeRl}/lib/site_perl";
       $ENV{LD_LIBRARY_PATH} = "$ENV{LD_LIBRARY_PATH}:$ENV{ORACLE_HOME}/lib32:$ENV{ORACLE_HOME}/lib";
       exec "$ENV{OrAcLePeRl}/bin/perl", $0, @ARGV;
    }
}

use strict;
use warnings;

use DBI;

my($username, $password) = ...;

my $dbh = DBI->connect("dbi:Oracle:$ENV{ORACLE_SID}", $username, $password) or die;
...

Also see

• Example Perl scripts (this site)

External links

• CPAN - The Comprehensive Perl Archive Network
• dbi.perl.org - Perl DBI Home Page
• DBD::Oracle - Perl Database Interface driver for Oracle