Introduction to using DBI and Oracle

Oracle Tips by Mladen Gogala

Introduction

DBI is a Perl module, well documented in both online sources and the "Programming the Perl DBI"  book by  Alligator Descartes, Alistair Carty, Tim Bunce and Linda Mui (O'Reilly, ISBN:1565926994) but still, I can see questions about using DBI with Oracle on Oracle USENET groups. That motivated me to write this article.

Oracle has many features and data types, while using DBI with each particular feature is not always entirely trivial. The purpose of this article is to cover many of those uses from  Perl practitioner's point of view. Here is what you can find in this article:

• Connecting to Oracle with the Perl DBI
   
• Executing SQL with the Oracle Perl DBI
   
• Introduction to Bind calls with the Oracle Perl DBI
   
• LOB Types and REF Cursors with Oracle Perl DBI
   
• Array, TAF & load/unload features of DBI/DBD::Oracle

The sole purpose of this article is to serve as an introductory reading for those who have never used the two together. Therefore, some knowledge of both Oracle and Perl is assumed, and although I will not try to show off my obfuscation abilities, this article still assumes that the reader has read “Learning Perl” and has some experience with Oracle RDBMS.

This article will not cover basic features of neither Perl nor Oracle. It will also not cover the installation of Perl, Oracle, DBI or DBD::Oracle. Furthermore, it makes no claims of completeness. This is simply an introduction to using Oracle with Perl DBI.

This article  was created on Red Hat Fedora Core 4, with  Oracle 10.2.0.2 and Perl 5.8.6. The decision not to cover the installation was made because the installation is different for each operating system, while I'd like to concentrate on the common features, the features that  can be used across the whole range of supported systems. Describing installation on Unix or Linux and omitting Windows or VMS would open me for the accusations of being OS biased. As I really am biased toward one type of OS, I wanted to hide that fact and cover just the common parts.

In writing this article I was relying mostly on the experience, scripts and sins of the past and online documentation. The most accurate and up to date online DBI documentation can always be found on CPAN (http://search.cpan.org). In particular, the syntax of all DBI methods can be found on the DBI page. I will occasionally quote DBI documentation, where appropriate and will always warn the reader when I’m doing so. Quotes from the online documentation will also be marked by the different font (smaller and italicized). The online documentation for the DBI and DBD::Oracle can be found at:

http://search.cpan.org/~timb/DBI-1.50/DBI.pm

http://search.cpan.org/~timb/DBD-Oracle-1.16/Oracle.pm

This is, of course, the documentation for the versions used in this article. This documentation is likely change with versions. If your DBI and DBD::Oracle versions are different from what was used to produce this article and  the example scripts within it, you should check the documentation for your favorite version.

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

Executing SQL with DBI

Now that we have a database handle properly created and configured, we can use it to execute SQL commands. Oracle SQL executes in several phases:

Parse phase: SQL statement is checked for syntactical validity and all objects are checked to see whether they exist or not and whether the user has appropriate access privileges or not. It is during this phase that the optimizer is invoked, statistics examined and the optimal access path determined. This parsing operation can be excruciatingly expensive and is best avoided, if at all possible.

Bind phase: placeholders in SQL statements are “connected” to the program variables. During this process the address of program variables is “made known” to oracle, so that it can read or write values from it. The same SQL command can be executed over and over again, without the need for re-parsing for various values of the program variable.

Execute phase: In this phase, the SQL statement in question is executed and the appropriate program areas within the program are updated to reflect that. Oracle can postpone parsing until the statement is actually executed, therefore decreasing the number of calls to oracle and the number of round trips over the network, necessary to reach the database.

Define phase: define phase exists only for queries. During this phase we define variables to receive output. This phase is not really necessary with Perl DBI as DBI calls also create variables to receive data.

Fetch phase: during the fetch phase, data is retrieved from an Oracle cursor and stored into program variables.  Fetch is not bi-directional, it can be read sequentially and closed when no longer needed.

These phases of execution correspond to DBI calls. In particular, “parse” phase corresponds to the prepare DBI call. Now is the right time to demonstrate the fabled “deferred parse:

Example 1.

#!/usr/bin/perl -w

use strict;

use DBI;

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

    || die( $DBI::errstr . "\n" );

$db->{AutoCommit}    = 0;

$db->{RaiseError}    = 1;

$db->{ora_check_sql} = 0;

$db->{RowCacheSize}  = 16;

my $SEL = "invalid SQL statement";

my $sth = $db->prepare($SEL);

print "If you see this, parse phase succeeded without a problem.\n";

$sth->execute();

print "If you see this, execute phase succeeded without a problem.\n";

END {

    $db->disconnect if defined($db);

}

The result of this script is the following:

bash-3.00$ /tmp/ttt

If you see this, parse phase succeeded without a problem.

DBD::Oracle::st execute failed: ORA-00900: invalid SQL statement (DBD ERROR: OCIStmtExecute) [for Statement "invalid SQL statement"] at /tmp/ttt line 13.

DBD::Oracle::st execute failed: ORA-00900: invalid SQL statement (DBD ERROR: OCIStmtExecute) [for Statement "invalid SQL statement"] at /tmp/ttt line 13.

As you can see, parse succeeded without a problem, although the SQL statement in the variable $SEL was clearly invalid. There is one more thing in this script that needs to be explained, namely the END block. The END block gets executed when the script exits, regardless of the way it exits. So , let’s comment it out and see what happens:

bash-3.00$ /tmp/ttt

If you see this, parse phase succeeded without a problem.

DBD::Oracle::st execute failed: ORA-00900: invalid SQL statement (DBD ERROR: OCIStmtExecute) [for Statement "invalid SQL statement"] at /tmp/ttt line 13.

DBD::Oracle::st execute failed: ORA-00900: invalid SQL statement (DBD ERROR: OCIStmtExecute) [for Statement "invalid SQL statement"] at /tmp/ttt line 13.

Issuing rollback() for database handle being DESTROY'd without explicit disconnect().

bash-3.00$

The last message was issued by the DBI, because of exiting without disconnect. To avoid that, it is a good practice to include an END block like in the example 1. End block can also be used to issue rollback after a failed transaction. To summarize the example 1, a successful “connect” call established a database handle $db. Database handle has a prepare method, used to create a statement handle, $sth. Statement handle has many methods. Execute, bind and fetch are all methods of a statement handle.

Now, let’s replace the invalid SQL with a valid one, namely “select * from emp”; Our script will need more then cosmetic changes. The script will now look like this:

Example 2.

The output looks exactly as expected:

So, now we not only have execute, we also have fetch, implemented by fetchrow_array method of the statement handle. The array @row is created on the fly, for each row, by the fetchrow_array method. Therefore, we do not have to do “define” as we would have had, had we been programming an OCI program instead of a Perl script.

There several more methods for fetching data from an Oracle cursor:

l       fetchrow_arrayref

l       fetchrow_hashref

l       fetchall_arrayref,

l       fetchall_hashref

These methods differ by what do they return (hash or array reference) and how many rows do they return  (one or all). Methods returning hash reference are convenient when we want to pick column value by column name, not its sequence number. For  methods that return reference to hash, retrieval works like this:

$row=sth->fetchrow_hashref();

$ename=$row->{ENAME};

Methods that fetch all rows are convenient when the underlying query returns relatively few rows that are frequently referenced within a script.

All those methods fetch  relational NULL as “undef” value, which is a problem if we have “use strict” in effect. That is the reason for having the line which reads:

                 $_ = "\t" if !defined($_);

in the script. If that line is commented out, we get an ugly output, laced with warning messages, which looks like this:

bash-3.00$ ./ttt

Use of uninitialized value in concatenation (.) or string at line 17

7369    SMITH   CLERK   7902    17-DEC-80       800

7499    ALLEN   SALESMAN        7698    20-FEB-81       1600

7521    WARD    SALESMAN        7698    22-FEB-81       1250

Use of uninitialized value in concatenation (.) or string at line 17

7566    JONES   MANAGER 7839    02-APR-81       2975

7654    MARTIN  SALESMAN        7698    28-SEP-81       1250

Use of uninitialized value in concatenation (.) or string at line 17

7698    BLAKE   MANAGER 7839    01-MAY-81       2850

Use of uninitialized value in concatenation (.) or string at line 17

7782    CLARK   MANAGER 7839    09-JUN-81       2450

Use of uninitialized value in concatenation (.) or string at line 17

7788    SCOTT   ANALYST 7566    09-DEC-82       3000

Use of uninitialized value in concatenation (.) or string at line 17

Use of uninitialized value in concatenation (.) or string at line 17

7839    KING    PRESIDENT               17-NOV-81       5000

7844    TURNER  SALESMAN        7698    08-SEP-81       1500

Use of uninitialized value in concatenation (.) or string at line 17

7876    ADAMS   CLERK   7788    12-JAN-83       1100

Use of uninitialized value in concatenation (.) or string at line 17

7900    JAMES   CLERK   7698    03-DEC-81       950

Use of uninitialized value in concatenation (.) or string at line 17

7902    FORD    ANALYST 7566    03-DEC-81       3000

Use of uninitialized value in concatenation (.) or string at line 17

7934    MILLER  CLERK   7782    23-JAN-82       1300

bash-3.00$

The strict checking, enforced by the “use strict” pragma, will flag out any NULL values encountered by our script. That must be handled if we don’t want a warning message after each NULL value.

This was a very simple script, executing and retrieving results of  the simplest possible query from the SCOTT.EMP table. The additional question one can immediately ask is the following:

I don’t know much about the EMP table and SCOTT demo schema. How many columns are returned, what are their names, types and sizes? How many rows were returned by the query? Of course, one solution is to scold the unfortunate wannabe programmer and tell him to go and read the fine manual, but RTFM is not always considered a polite and civilized solution, so I developed a DBI solution, presented in an extended version of the example 2:

Example 2a.

Of course, the most interesting thing here is the output of this script, which looks like this:

This statement returns 8 fields

Field number 0: name EMPNO of type DECIMAL with precision 4,0

Field number 1: name ENAME of type VARCHAR2 with precision 10,0

Field number 2: name JOB of type VARCHAR2 with precision 9,0

Field number 3: name MGR of type DECIMAL with precision 4,0

Field number 4: name HIREDATE of type DATE with precision 75,0

Field number 5: name SAL of type DECIMAL with precision 7,2

Field number 6: name COMM of type DECIMAL with precision 7,2

Field number 7: name DEPTNO of type DECIMAL with precision 2,0

This query returned 14 rows.

So, what is the answer to our question? Column name, column type, column precision and scale, as well as the number of columns returned are all attributes of the statement handle. Those attributes are defined when the statement is parsed. In our script, we bundled the parse phase with the execute phase, which means that those attributes are not defined until we execute the handle, which is a colloquial expression for “invoke the handle’s execute method”. If we have a valid statement handle $sth,  that handle automatically has the following elements:

• $sth->{NUMBER_OF_FIELDS} is an integer field telling us how many columns does the query return, if the statement is a query.
• $sth->{NAME} is an array, with  $sth->{NUMBER_OF_FIELDS} elements, each element being the column name of the corresponding returned column.
• $sth->{TYPE} is also an array which contains column types. Column types are integers, because they're consistent across databases. Supported types depend on the version of the DBI and can be translated into type names by using the type_info() method as shown in the example 2a.
• $sth->{PRECISION} and $sth->{SCALE} are both arrays containing the information about the precision and the scale of the returned column. Note that the precision for VARCHAR2 column is its size. Its scale, of course is 0. The value of scale traditionally defines the number of decimal places in the number field.
• Last but not least is the rows method. Invoking $sth->rows() will give you the number of rows fetched so far or the number of affected rows. This method is completely analogous to the PL/SQL ROWCOUNT attribute and is used in exactly the same way.
• There are, of course, other elements which can be located and studied on the online documentation page.

Generally speaking, statement handles are Oracle cursors, disguised as Perl objects. The whole philosophy of  database handles and statement handles is the same as the philosophy of database connections and cursors: to open a cursor, one has to connect to a database. In the OO world of the modern programming languages, objects have “methods” and “properties” or “members”, so those are the terms that we must use when talking about DB handles  and statement handles.

Now we know how to execute a simple SQL statement and get the basic information about it. For statements like “ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR'”, there is also a shortcut:

$db=DBI->connect(“dbi:Oracle:Local”,”scott”,”tiger”);

$db->do(“ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR'”);

No prepare is necessary, no statement handle is available, the “do” method is a database handle method which fuses prepare and execute methods in a single step. That is convenient for the alter session commands or DDL commands. The next thing to learn is how to make a program variable “known” to Oracle.

Binding Variables with Oracle DBI

The problem at hand, the one that will be solved by binding variables is the following: we need to find out name, job, department name and salary for an employee, by the employee number. The employee number is read from standard input. The SQL statement that we will use to solve the problem looks like this:

The expression “:EMPNO” is called a “placeholder” and has to be defined before the statement can be executed.  Connecting a placeholder with a program variable is known as binding . Perl supports several types of binds. Let’s see the most popular and the simplest one first:

Example 3

This script produces the following output:

bash-3.00$ ./ttt

Enter EMPNO:7934

Employee Name       Job                   Department             Salary

-------------------------------------------------------------------------------

MILLER                  CLERK           ACCOUNTING      1300

In case that a non-existing employee number is entered, no output is produced. So, here we have a script that reads a value from the standard input, and needs that value to complete the SQL execution. The placeholder used here is a simple question mark: “?” :“WHERE empno=?”. That is a DBI specific trick, later emulated in other languages and frameworks, like PEAR::DB. The trick is that DBI uses so called positional bind, in which each question mark is a placeholder, replaced by  its sequence number. It can’t be simpler then that, but there is a problem:

If our SQL statement was something like:

 WHERE empno = ? and  ?<10000

2 different placeholders would be needed. If a condition includes the same placeholder appearing in several places in the same SQL, positional binds can not be used, we have to resort to the named binds. Named binds differ from positional binds in such a way that placeholders are not simple question marks but are named instead. As a matter of fact, our first example was a part of a named bind sequence. The statement below

will lead to $sth->bind_param(“:EMPNO”, $empno); Other then that, positional binds are identical to the named ones. I also find named binds easier to follow and less confusing then a forest  of question marks that I have to count by using my fingers in order to really understand the underlying SQL. I have only 10 fingers, which is somewhat limiting when it comes to really complex SQL statements. I also adopted the naming convention which mandates naming placeholders the same as the variables they’re being bound to, only in capital letters, just like in the code snippet above.

So far, Oracle has just been reading script variables and using them in SQL. What happens if Oracle needs to write something into a script variable? In other words, what happens if the placeholder is in place of the input/output  parameter in PL/SQL procedure? It turns out that we need somewhat more complex bind. In order to do that, let’s  take a look at the following  PL/SQL script:

In a Perl script, we would have to call it, by using a snippet like this:

my $SCR=”begin

                            dbitest(:EMPNO,:DNAME);

                    end;”;

Unfortunately, the bind method we’ve studied so far cannot help us here. In other words, using calls like $sth->bind_param(“:ENAME”,$ename) and $sth->bind_param(“:DNAME”, $dname) would produce nothing but a nasty error message. Let’s modify our example 3 and demonstrate:

Example 3a (Doesn’t work)

The execution produces the following:

That is not nice. So, how do we fix that? DBI has another type of bind method, used for PL/SQL procedures, LOB parameters or ref cursors. This method is called bind_param_inout. There are  two important differences between bind_param and bind_param_inout:

• Bind_param takes a program variable as an argument while bind_param_inout takes a REFERENCE to program variable as an argument. In other words, we’re passing a pointer to variable to Oracle, not the variable itself.
   
• Specifying the maximum length of the bind variable is mandatory for bind_param_inout.

Bind using the value of the variable, like bind_param are also called binds by value, as the value of the variable is made available to Oracle. Binds using bind_param_inout are also known as binds by reference or binds by address as reference (address) of a Perl variable is made known to Oracle.

So, let’s now modify our example 3a  to use bind by reference:

Example 3b

The execution now proceeds in much nicer fashion:

bash-3.00$ ./ttt

Enter EMPNO:7934

Department name: ACCOUNTING

The only difference is the use of bind_param_inout  instead of bind_param in the “b” version. Can the “inout” version of the call be used for simple querying as in the example 3? Yes, it can, but it is simpler to use binds by value then binds by address. Interestingly enough both binds by value and binds by address (binds by reference) support positional binds, with “?”  for placeholders. Binds by reference also have to be used with RETURNING INTO clause as a part of an INSERT statement.

So far, this article has covered connecting to Oracle, simple fetches, binding variables to placeholders and executing PL/SQL scripts. To really spice it up, this article will also cover working with LOB data types and with REF cursors.

LOB Types and REF Cursors with Oracle DBI

REF cursors are an extremely handy way of returning sets of records. Before proceeding with Perl scripting, let’s first create PL/SQL code that will help us demonstrate the concept:

Let’s say we want to execute the following PL/SQL code in our  Perl script:

BEGIN

       DBI.TEST(:DNO,:CSR);

END;

The key to success lies in understanding  what exactly is our REF cursor variable. REF Cursor variables are Oracle’s way of returning a statement handle.  I strongly believe that an example is worth a thousand words, so here it is:

Example 4

When executed, this code will produce the following output:

bash-3.00$ ./ttt

Now, we have a valid handle....

Employee         Job                           Hiredate       Salary

-------------------------------------------------------------------

CLARK            MANAGER    09-JUN-81      2450

KING             PRESIDEN   17-NOV-81      5000

MILLER           CLERK      23-JAN-82      1300

So, what can we see in this example?  First, we had to explicitly include DBD::Oracle, with the type definitions that are not normally exported by the module. Second, we had to do an “inout” bind, with size set to 0 and with a hash reference which tells DBI  the oracle type of the variable being bound. Third, REF Cursor variables are, in essence, statement handles. When a PL/SQL procedure returns REF Cursor variable, it is just the same as if the handle was produced by using $db->prepare.

What is the most frequent use of REF Cursor variables? I’m a database administrator and I’ve never had to use them, I only needed to support them when the real programmers were using them. The most frequent situations involving use of the REF Cursor variables were migration projects where the source database used result sets as a return from a stored procedure. Databases that are known for encouraging such development style are  Sybase and SQL Server. Any migration of a program which runs against SQL Server or Sybase to a Perl script that runs against Oracle database is likely to need REF Cursor variables. Of course, this part of the article would not be complete without explanation of the “real programmers” reference. For those among you who want to become real programmers, here are some definitions:

http://www.toodarkpark.org/textfile.cgi/computers/humor/real-programmers

I can only wish you luck with your further career endeavors.  Real programmers aside, let’s return to DBI and Oracle. REF Cursor is a special column type that warrants inclusion of DBD::Oracle directly, in order to gain access to the definition of types. The same is true for LOB data types. LOB is an abbreviation for “Large Objects”.  LOB types are used when we need to store large documents, images or other types of media files into the database.

Good thing about the LOB data types is that it is mostly handled automatically by the DBI itself. There are two separate cases which need handling: inserting LOB data and selecting LOB data. To demonstrate loading LOB data into the database, we'll need some database infrastructure:

Unfortunately, normal  Oracle demo tables, belonging to SCOTT do not contain LOB columns. No problems, we have just created everything needed for such a demo. The next script will read the file name from the command line argument and read the whole file into a memory buffer. That memory buffer will then be inserted as a BLOB into the database.

Example 5