Connecting to Oracle with the Perl DBI

Oracle Tips by Mladen Gogala

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 simple:

$ perl -e 'use DBI; print $DBI::VERSION,"\n";'

1.50

$ perl -e 'use DBD::Oracle; print $DBD::Oracle::VERSION,"\n";'

1.17

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:

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 Oracle instance:

my $db=DBI->connect("dbi:Oracle:local", "scott", "tiger");

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 specific.
   
• 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:

    $dbh->{AutoCommit}    = 0;

    $dbh->{RaiseError}    = 1;

    $dbh->{ora_check_sql} = 0;

    $dbh->{RowCacheSize}  = 16;

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 production environment.

For a complete treatment of PHP and Oracle, see Mladen Gogala's great new book "Easy Oracle PHP".  This unique book focuses on removing the guesswork from creating dynamic web pages with Oracle content, and it's only $19.95.