Connecting to Oracle with the Perl DBI
Oracle Tips by Mladen Gogala
Mladen Gogala is the author of "Easy
See the whole article:
Perl DBI and Oracle
|Mladen Gogala is available to assist you
with your toughest Perl DBI problems. Just call.
So, let's start with the article. In order to
execute the examples from the article the reader will have to have
access to an Oracle database and also access to Perl interpreter
with DBI and DBD::Oracle installed. Finding Perl isn't a problem.
Verifying that all necessary modules are installed is also very
easy. So, how do we tell that DBI and DBD::Oracle are installed and
how do we tell which versions are installed? The answer is really
$ perl -e
'use DBI; print $DBI::VERSION,"\n";'
$ perl -e
'use DBD::Oracle; print $DBD::Oracle::VERSION,"\n";'
Therefore, we have DBI 1.50 and and DBD::Oracle
1.17. At the moment, these are the latest and the greatest
versions. Here is the Oracle version:
SQL*Plus: Release 10.2.0.2.0 - Production on Sun Apr 16 23:09:35
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 -
the Partitioning, OLAP and Data Mining options
This username and password (SCOTT/TIGER) will
be used throughout this article, as well as the accompanying EMP and
DEPT tables which will be used for almost all of the examples. EMP
and DEPT tables do not have LOB columns, so I will have to create my
own infrastructure for dealing with LOB data types.
Connecting to Oracle with the Perl DBI
DBI uses the following syntax to connect to an
The string on the beginning contains the
following elements, separated by colons: the string "dbi", driver
type, in this case "Oracle" and the database name, also known as TNS
descriptor. In the line above, the database name was "local". The
first argument, therefore, defines the database to connect to. The
2nd and 3rd arguments are, of course, username and password. Of
course, everyone who has ever worked with databases knows that
connection errors happen from time to time, so it is prudent to
check for errors:
my $dbh =
DBI->connect( "dbi:Oracle:$db", $username, $passwd ) ||
die( $DBI::errstr . "\n" );
In case of connection error, database handle $dbh
is not created and the error string "errstr" comes from the DBI
class itself. If, on the other hand, the connection attempt is
successful and the database handle is created, there are several
properties that can be very helpful with further programming:
- AutoCommit: when set to 1, DBI
issues a commit after each successful SQL statement. This is
very dangerous and on by default. Setting it to 0 is a good
idea. This handle property is not Oracle specific, it is
available for any database.
- RaiseError: When turned on (it is
off by default) it sends an exception to your script and
terminates it. It is a good idea to kill the script if a
non-handled Oracle exception happens. This handle property is
not Oracle specific, it is available for any database.
- ora_check_sql: Oracle has a
performance enhancing trick called "deferred parse". When used,
it decreases the number of the needed database calls, by
bundling "parse" and "execute" phases of the SQL execution.
Unfortunately, this feature is not turned on by default. To turn
it on, you have to set ora_check_sql to 0. This is Oracle
- RowCacheSize: this instructs the
driver to create a local pre-fetch cache and defines its size.
- Both DBI database and statement handles
always have errstr and err member variables which
contain error message and code respectively. In case of a failed
connection attempt, when database handle is not created, errstr
and err can be used in class context, as $DBI::errstr and $DBI::err.
That is used in the examples throughout the article.
- This list is by no means complete, I
mentioned only the properties most frequently used in the
scripts. For the complete list, please consult the online DBI
documentation. In further text, I'll introduce two more handle
properties, used to deal with LOB data types.
These properties are keys of an associative
array, and here is how they're set:
So, this turns off auto-commit, instructs DBI
to terminate the script if SQL error is incurred, instructs Oracle
driver to use deferred parse and wait with parsing the SQL until an
execution is attempted for the first time. It also creates a local
pre-fetch cache with place for 16 rows.
Unfortunately, DBD::Oracle does not support
array interface, so the effects of this local cache are not as great
as one would expect. As a matter of fact, this lack of support for
Oracle array interface is the feature that severely limits the use
of Perl with Oracle and makes it unsuitable for large data loads or
massive transactions. Features that are also not supported are TAF
(Transparent Application Failover) and direct loads. In other words,
Perl with DBI and DBD::Oracle is not an industry strength tool for a
||For a complete treatment of PHP and Oracle,
see Mladen Gogala's great new book "Easy
This unique book focuses on removing the guesswork from
creating dynamic web pages with Oracle content, and it's