Using Perl DBI with Oracle

I have been using Perl scripts extensively for a couple of years now to analyze data and generate charts and tables. However, I have only recently investigated using Perl to communicate with an Oracle database.

This post was researched using Perl v5.10 with Oracle 11.2.0.2 Express Edition on Windows XP 32-bit. The examples are based on a table listing drivers in the 2011 Formula One World Championship.

The example table is defined as follows:

CREATE TABLE driver
(
  key NUMBER,
  name VARCHAR2(30),
  team VARCHAR2(30),
  points NUMBER
);

The following rows were inserted into the table (based on championship standings after 17 of the 19 races:

INSERT INTO driver VALUES (1,'Sebastian Vettel','Red Bull',374);
INSERT INTO driver VALUES (2,'Jenson Button','McLaren',240);
INSERT INTO driver VALUES (3,'Fernando Alonso','Ferrari',227);
INSERT INTO driver VALUES (4,'Mark Webber','Red Bull',221);
COMMIT;

Single Row Select

The first script counts the number of rows in the DRIVER table. Although trivial this script demonstrates the basic components of all Perl DBI scripts.

use DBI;

if (!($dbh = DBI->connect ('DBI:Oracle:XE',"us01","us01")))
{
  die ("Failed to connect to database: " . DBI->errstr);
};

$sql = "SELECT COUNT(*) FROM driver";

($count) = $dbh->selectrow_array ($sql);

printf ("Count = %d\n",$count);

$dbh->disconnect;

The use DBI pragma instructs Perl to load the Database Interface (DBI) module. I normally use the strict pragma, but have omitted it within this post to improve clarity.

A connection must be created before any statements are executed. A successful connection returns what is referred to as a database handle. Most Perl scripts seem to use $dbh for the database handle.

In this example the database SID is XE and therefore the connect string is DBI:Oracle:XE. In my highly unsecure database both the username and password are us01.

I like to define the SQL statement using a separate variable ($sql). This is not necessary, but in my opinion results in clearer and more supportable code as the length and complexity of the SQL statements increases.

In this example the query can only return one row. I have used the selectrow_array subroutine to execute the query and to fetch the result. The row is returned into the $count variable.

Finally the script disconnects from the database.

The script should return the following output:

Count = 4

Multi-Row Select

This example demonstrates how to select multiple rows from a cursor.

use DBI;

if (!($dbh = DBI->connect ('DBI:Oracle:XE',"us01","us01")))
{
  die ("Failed to connect to database: " . DBI->errstr);
};

$sql =
  "SELECT name,team,points ".
  "FROM driver ".
  "ORDER BY points DESC";

if (!($sth = $dbh->prepare ($sql)))
{
  die ("Failed to prepare statement: " . DBI->errstr);
};

$sth->execute;

while (@data = $sth->fetchrow_array())
{
  $name = $data[0];
  $team = $data[1];
  $points = $data[2];
  printf ("%-20s %-20s %3d\n",$name,$team,$points);
}

$sth->finish;

$dbh->disconnect;

In this example the SQL statement is parsed using the prepare subroutine. This returns a statement handle ($sth) which will be used to refer to the statement throughout the rest of the script.

This query does not have any bind variables, so it can be executed using the execute subroutine. Rows can then be fetched one at a time using the fetchrow_array subroutine which returns an array of results. In this case I have assigned each array element to a separate variable to demonstrate that the array is zero-based. When all required rows have been fetched from the cursor the statement can be closed using the finish subroutine.

This script generates the following output:

Sebastian Vettel Red Bull 374
Jenson Button McLaren 240
Fernando Alonso Ferrari 227
Mark Webber Red Bull 221

The following script demonstrates a slightly different way of coding the fetch:

use DBI;

if (!($dbh = DBI->connect ('DBI:Oracle:XE',"us01","us01")))
{
  die ("Failed to connect to database: " . DBI->errstr);
};

$sql =
  "SELECT name,team,points ".
  "FROM driver ".
  "ORDER BY points DESC";

if (!($sth = $dbh->prepare ($sql)))
{
  die ("Failed to prepare statement: " . DBI->errstr);
};

$sth->execute;

while (($name,$team,$points) = $sth->fetchrow_array())
{
  printf ("%-20s %-20s %3d\n",$name,$team,$points);
}

$sth->finish;

$dbh->disconnect;

In the above example each row is fetched directly into a list of variables.

The second script generates the same output:

Sebastian Vettel Red Bull 374
Jenson Button McLaren 240
Fernando Alonso Ferrari 227
Mark Webber Red Bull 221

Both the above methods are functionally equivalent and the choice of one or the other is simply a matter of personal taste.

However, there is a third way to achieve the same results. This method, however, is slightly different:

use DBI;

if (!($dbh = DBI->connect ('DBI:Oracle:XE',"us01","us01")))
{
  die ("Failed to connect to database: " . DBI->errstr);
};

$sql =
  "SELECT name,team,points ".
  "FROM driver ".
  "ORDER BY points DESC";

if (!($sth = $dbh->prepare ($sql)))
{
  die ("Failed to prepare statement: " . DBI->errstr);
};

$sth->execute;

$array = $sth->fetchall_arrayref ();

foreach $row (@$array)
{
  ($name,$team,$points) = @$row;
  printf ("%-20s %-20s %3d\n",$name,$team,$points);
}

$sth->finish;

$dbh->disconnect;

In the above example, the fetchall_arrayref call fetches all rows into $array. This array can subsequently be processed without any further communication with the database. In some circumstances this technique could be very efficient - for example returning the top ten results. It could be less efficient in other circumstances particularly if a large number of rows may be returned or the later rows are likely to be discarded.

The third script generates the same output as its predecessors:

Sebastian Vettel Red Bull 374
Jenson Button McLaren 240
Fernando Alonso Ferrari 227
Mark Webber Red Bull 221

Bind Variables

We will normally want to use bind variables with SQL queries. The following example uses a bind variable to select the rows for a single team - in this case Red Bull:

use DBI;

if (!($dbh = DBI->connect ('DBI:Oracle:XE',"us01","us01")))
{
  die ("Failed to connect to database: " . DBI->errstr);
};

$sql =
  "SELECT name,team,points ".
  "FROM driver ".
  "WHERE team = ?";

if (!($sth = $dbh->prepare ($sql)))
{
  die ("Failed to prepare statement: " . DBI->errstr);
};

$sth->bind_param (1,"Red Bull");

$sth->execute;

while (@data = $sth->fetchrow_array())
{
  $name = $data[0];
  $team = $data[1];
  $points = $data[2];
  printf ("%-20s %-20s %3d\n",$name,$team,$points);
}

$sth->finish;

$dbh->disconnect;

In Perl bind variables are represented by a "?" in the SQL statement text. Each bind variable should have an associated bind_param call. Bind parameters are numbered from 1 upwards.

You can avoid calls to bind_param by listing bind variables in the execute call:

use DBI;

if (!($dbh = DBI->connect ('DBI:Oracle:XE',"us01","us01")))
{
  die ("Failed to connect to database: " . DBI->errstr);
};

$sql =
  "SELECT name,team,points ".
  "FROM driver ".
  "WHERE team = ?";

if (!($sth = $dbh->prepare ($sql)))
{
  die ("Failed to prepare statement: " . DBI->errstr);
};

$sth->execute ("Red Bull");

while (($name,$team,$points) = $sth->fetchrow_array())
{
  printf ("%-20s %-20s %3d\n",$name,$team,$points);
}

$sth->finish;

$dbh->disconnect;

In the above example the bind variable value ("Red Bull") is specified as the first parameter in the execute call. Any number of bind variables can be specified in this call.

INSERT statements

The following script is an example of a simple INSERT statement:

use DBI;

if (!($dbh = DBI->connect ('DBI:Oracle:XE',"us01","us01")))
{
  die ("Failed to connect to database: " . DBI->errstr);
};

$sql = "INSERT INTO driver VALUES (5,'Lewis Hamilton','McLaren',202)";

if (!($dbh->do ($sql)))
{
  die ("Failed to insert row: " . DBI->errstr);
};

$dbh->disconnect;

In the above script literal values have been specified for each column value.

The do subroutine allows us to execute a simple statement without the need to explicitly initialize a statement context.

Bind variables can also be specified. For example:

use DBI;

if (!($dbh = DBI->connect ('DBI:Oracle:XE',"us01","us01")))
{
  die ("Failed to connect to database: " . DBI->errstr);
};

$sql = "INSERT INTO driver VALUES (?,?,?,?)";

if (!($sth = $dbh->prepare ($sql)))
{
  die ("Failed to prepare statement: " . DBI->errstr);
};

$sth->execute (6,"Felipe Massa","Ferrari",98);

$dbh->disconnect;

In the above example the bind variables are listed as parameters in the execute call.

It is also possible to set bind variables using the bind_param call. For example:

use DBI;

if (!($dbh = DBI->connect ('DBI:Oracle:XE',"us01","us01")))
{
  die ("Failed to connect to database: " . DBI->errstr);
};

$sql = "INSERT INTO driver VALUES (?,?,?,?)";

if (!($sth = $dbh->prepare ($sql)))
{
  die ("Failed to prepare statement: " . DBI->errstr);
};

$sth->bind_param (1,7);
$sth->bind_param (2,'Nico Rosberg');
$sth->bind_param (3,'Mercedes');
$sth->bind_param (4,75);

$sth->execute;

$dbh->disconnect;

Note that bind parameters are numbered from 1 upwards.

In this example the primary key column could be generated automatically using a sequence. We can create the sequence as follows:

CREATE SEQUENCE driver_sequence START WITH 8;

The sequence starts at 8 as the table already contains 7 rows.

We can then create an INSERT trigger on the DRIVER table as follows:

CREATE OR REPLACE TRIGGER driver_trigger
BEFORE INSERT ON driver
FOR EACH ROW
DECLARE
  l_key NUMBER;
BEGIN
  SELECT driver_sequence.NEXTVAL INTO l_key FROM dual;
  :new.key := l_key;
END;
/

When we insert rows into the DRIVER table we no longer need to specify a key as this will be generated by the trigger.

However we might wish to know the value of the internally generated key. In this case we can use the RETURNING clause with the INSERT statement. For example:

use DBI;

if (!($dbh = DBI->connect ('DBI:Oracle:XE',"us01","us01")))
{
  die ("Failed to connect to database: " . DBI->errstr);
};

$sql =
  "INSERT INTO driver (name,team,points) ".
  "VALUES (?,?,?) ".
  "RETURNING key INTO ?";

if (!($sth = $dbh->prepare ($sql)))
{
  die ("Failed to prepare statement: " . DBI->errstr);
};

$key = 0;

$sth->bind_param (1,'Michael Schumacher');
$sth->bind_param (2,'Mercedes');
$sth->bind_param (3,70);
$sth->bind_param_inout (4,$key,SQL_NUMERIC);

$sth->execute;

printf ("Key = %d\n",$key);

$dbh->disconnect;

Note that the RETURNING clause effectively declares a fourth bind variable. This variable differs from the others as it accepts a return value and is therefore declared as an INOUT parameter. Note that the $key parameter is passed by reference (not value) in the bind_param_inout call and is therefore preceded by a backslash.

The above query returns the following output:

Key = 8

UPDATE Statements

As with INSERT statements the simplest form of an UPDATE statement uses literal values and the do subroutine:

use DBI;

if (!($dbh = DBI->connect ('DBI:Oracle:XE',"us01","us01")))
{
  die ("Failed to connect to database: " . DBI->errstr);
};

$sql = "UPDATE driver SET points = 399 WHERE key = 1";

if (!($dbh->do ($sql)))
{
  die ("Failed to update row: " . DBI->errstr);
};

$dbh->disconnect;

The above example adds 25 points to Sebastian Vettel's current score of 374 points.

Alternatively we could use bind variables to achieve the same result. For example:

use DBI;

if (!($dbh = DBI->connect ('DBI:Oracle:XE',"us01","us01")))
{
  die ("Failed to connect to database: " . DBI->errstr);
};

$sql = "UPDATE driver SET points = ? WHERE key = ?";

if (!($sth = $dbh->prepare ($sql)))
{
  die ("Failed to prepare statement: " . DBI->errstr);
};

$sth->execute (399,1);

$dbh->disconnect;

In this example the bind variable values are supplied as arguments to the execute call.

We can also use bind_param calls to achieve the same objective:

use DBI;

if (!($dbh = DBI->connect ('DBI:Oracle:XE',"us01","us01")))
{
  die ("Failed to connect to database: " . DBI->errstr);
};

$sql =
  "UPDATE driver ".
  "SET points = points + ? ".
  "WHERE key = ?";

if (!($sth = $dbh->prepare ($sql)))
{
  die ("Failed to prepare statement: " . DBI->errstr);
};

$sth->bind_param (1,25);
$sth->bind_param (2,1);

$sth->execute;

$dbh->disconnect;

In the above example bind parameters have been used to specify the number of points and also the primary key of the row to be updated.

Assume we want to know how many points Vettel has now scored. To avoid selecting the row we can specify the RETURNING clause to the UPDATE statement

use DBI;

if (!($dbh = DBI->connect ('DBI:Oracle:XE',"us01","us01")))
{
  die ("Failed to connect to database: " . DBI->errstr);
};

$sql =
  "UPDATE driver ".
  "SET points = points + ? ".
  "WHERE key = ? ".
  "RETURNING points INTO ?";

if (!($sth = $dbh->prepare ($sql)))
{
  die ("Failed to prepare statement: " . DBI->errstr);
};

$points = 0;

$sth->bind_param (1,25);
$sth->bind_param (2,1);
$sth->bind_param_inout (3,$points,SQL_NUMERIC);

$sth->execute;

printf ("Value = %d\n",$points);

$dbh->disconnect;

In the above example a call to bind_param_inout has been added to return the total number of points following execution of the UPDATE statement. In this case the script generates the following output:

Value = 399

DELETE statements

DELETE statements can use literal values or bind variables. For example:

use DBI;

if (!($dbh = DBI->connect ('DBI:Oracle:XE',"us01","us01")))
{
  die ("Failed to connect to database: " . DBI->errstr);
};

$sql =
  "DELETE FROM driver ".
  "WHERE key = ?";

if (!($sth = $dbh->prepare ($sql)))
{
  die ("Failed to prepare statement: " . DBI->errstr);
};

$sth->bind_param (1,8);

$sth->execute;

$dbh->disconnect;

In the above example the bind_param call is used to set the bind variable value to 8.

Transactions

The above examples do not include commit or rollback calls. This is because, by default, Perl DBI enables automatic commits. Few Oracle users will desire this behaviour. To disable automatic commits set the AutoCommit variable in the connection handle to 0. For example:

$dbh->{AutoCommit} = 0;

When automatic commits have been disabled, to commit a transaction use:

$dbh->commit;

To rollback the transaction use:

$dbh->rollback;

The following script disables automatic commits and then explicitly specifies a commit at the end of the transaction:

use DBI;

if (!($dbh = DBI->connect ('DBI:Oracle:XE',"us01","us01")))
{
  die ("Failed to connect to database: " . DBI->errstr);
};

$dbh->{AutoCommit} = 0;

$sql = "INSERT INTO driver (name,team,points) VALUES (?,?,?)";

if (!($sth = $dbh->prepare ($sql)))
{
  die ("Failed to prepare statement: " . DBI->errstr);
};

$sth->bind_param (1,'Michael Schumacher');
$sth->bind_param (2,'Mercedes');
$sth->bind_param (3,70);

$sth->execute;

$dbh->commit;

$dbh->disconnect;

The AutoCommit parameter is set after the connection has been created and before the first DML statement.

The commit call is executed before the session is disconnected.