4.2.1   Driver Handles

Driver handles are not normally referenced by programmers as they are loaded when DBI is initialized. The great thing about DBI is that the individual drivers are completely encapsulated within a handle. So it is possible and sometimes very useful to have more than one driver handle loaded at the same time. For example, if you need to transfer data from an Informix database into an Oracle database, it is very easy to write a single Perl script that connects to both and passes data between them.

4.2.2   Database Handles

These handles are an encapsulation of a single database connection and are always the children of the driver handle that created them. As with their parents, you can spawn any number of different connections to a single driver handle. In the example above, you could use two connections (database handles) for both Oracle and Informix DBs to account for an even more complicated data structure. Database handles are also thread-safe, so an individual handle will not leak into other handles. Database handles are by convention called $dbh in Perl scripts.

4.2.3   Statement Handles

Statement handles are the real meat of the DBI system, and as the name implies, they encapsulate single SQL statements to be executed on the database. Like the other handles, all statement handles are children of the database handle that created them, and like their parents, data will not leak out of them and into other handles. Statement handles are conventionally called $sth in Perl scripts.

4.3.1   Data Source Name (dsn)

Like all database APIs, DBI needs to be told how to connect to a database, where to find the database, and how to log into the database. In this tutorial, we are going to use the DBD::Oracle driver to tell DBI how to connect. DBI has its own simple syntax for specifying the DB Driver. It is dbi, followed by a colon :, then name of the driver, Oracle, followed by another colon :. So our data source will start with dbi:Oracle:. It is important to remember that this value is case--sensitive.

As we are using a local install of the 10XE database, it is best to keep the connection as simple as possible. So the connection call will look like this:

$dbh = DBI->connect(dbi:Oracle:, 'system', 'your_password');

In this case, DBI uses DBD::Oracle to connect to the database installed on the local computer using the userid "system" and the password "system". DBI also lets you connect in any number of other ways, depending on how you have set up your Oracle listener. For example:

$dbh = DBI->connect(dbi:Oracle:XE, 'system', 'your_password');

will also work (assuming you have not changed the default value in the default tnsnames.ora file).

$dbh = DBI->connect(dbi:Oracle:, 'system@XE', 'your_password');

Will also work, and even

$dbh = DBI->connect(dbi:Oracle: 'system@localhost/XE', 'your_password');

At connection time, you may also set specific attributes for that connection, including some that are specific to particular DBD drivers. These values can control how errors are handled, and how data is committed. The DBD::Oracle driver supports all of the standard attributes and has no custom attributes itself. For a complete list of the attributes, see the DBD::Oracle Perl doc.

4.3.2   Disconnecting from Oracle

Though DBI does not require you to explicitly disconnect a database connection before your program ends, it is always good practice to do so. DBI has two ways to disconnect. The first uses the database handle.

$dbh->disconnect();

Or you could use the DESTROY() method:

$dbh->DESTROY();

The first is the more common practice. It is also good form to disconnect with the statement handle.

$sth->finish();

in tandem with disconnect() as this will end any processes that may be ongoing in the statement handle. This will stop DBI from throwing warning errors if a statement is still running when you disconnect from Oracle.

4.5.1   Queries That Return No Rows

Many SQL statements do not return data, and DBI handles those as well. One could code such a statement like this:

$sth = $dbh->prepare("UPDATE locations SET city = 'Ottawa'
                      WHERE location_id = 1800");
$sth->execute();

However, this is a waste of resources, since you are creating a statement handle for a query that returns no rows. So the database handle has a built-in shortcut for just such a case. This is the do() method. The code below shows how it is used.

$rows_affected = $dbh->do("UPDATE locations SET city = 'Ottawa'
                           WHERE location_id = 1800");
print "Rows Affected = $rows_affected\n";

As an added bonus, with DBD::Oracle, the do() method will return the number of rows affected by the query or –1 if an error occurred.

4.5.2   Binding Parameters to Statements

We know now that DBI can run hard-coded SQL statements. But to be truly useful it must be able to bind variables to placeholders.

With the following SQL,

SELECT city FROM locations WHERE location_id = 1800

we would like to replace the 1800 with a placeholder. We could just use Perl for this, which would look something like this:

my $loc_id = 1800;
$sql = "SELECT city FROM locations WHERE location_id = $loc_id";

This is called in-line binding. It can work quite well for simple SQL, but what if we get an SQL statement such as this?

SELECT city FROM locations WHERE location_id = 'IT';

Almost identical statements, but we have to add nasty single quotes to make the statement work. So our Perl will look like this:

my $country_id = 'IT';
$sql = "SELECT city FROM locations
        WHERE location_id = '$country_id'";

This leaves us vulnerable to potential bugs. For example, if country_id happened to contain a single quote, you would have to write a whole procedure in Perl to account for it. Of course you could write all of your SQL in-line as long as you are careful with ' and other characters that SQL does not like. DBI even has a method on the database handle called quote(), to fix most of these problems for you. So you may never encounter a problem.

A couple caveats when using in-line binding. The first pertains to how Oracle optimizes SQL. As we know, when a query is run in Oracle, the optimizer analyzes and then saves a plan for the query. With in-line coded SQL the optimizer will not reuse execution plans in the same way that it can reuse execution plans where placeholders are used. So in the long run your DB will work faster when you use placeholders.

The second warning: when you use in-line binding, you may also be opening your system to SQL injection attacks.

4.5.3   Using Simple Placeholders

In DBI, the most simple placeholder is the ? character. So, if we make the following change to our first SQL like this:

$sql = 'SELECT city FROM locations WHERE location_id = ?';

the ? will act as our placeholder, and all you have to do to make it work is use the database handle’s prepare() method to get a statement handle.

$sth = $dbh->prepare('SELECT city FROM locations
                      WHERE location_id = ?');

Alternatively:

$sql = 'SELECT city FROM locations WHERE location_id = ?';
$sth = $dbh->prepare($sql);

You can then can pass the parameter to the statement handle with its execute() method.

$sth->execute(1800);

So our code would look like this:

#!/usr/bin/perl

use DBI;
use strict;
use vars qw($dbh $sth $sql);

my $loc_id = 'IT';
$dbh = DBI->connect('dbi:Oracle:', 'hr@localhost/XE', 'hr');
$sql = 'SELECT city FROM locations WHERE location_id = ?';
$sth = $dbh->prepare($sql);
$sth->execute($loc_id);

while (my ($r_city) = $sth->fetchrow())
{
    print " $r_city \n";
}

$sth->finish();
$dbh->disconnect();

If the $loc_id variable contained a ' character, as in IN'T, DBI would automatically fix this for us when you execute the SQL. So no worries about single quotes - DBI takes care of them, and you know the same execution plan will be reused no matter what value you enter for country_id.

4.5.4   Complex Placeholders

What about the case where you have SQL like this?

SELECT city FROM locations
WHERE
    (city = 'Rome' or city = 'New York')
    AND country_id <> 'IT'
    AND state_province <> 'New York'

This could have the parameters like the following.

SELECT city FROM locations
WHERE
    (city = ? or city = ?')
    AND country_id <> ?
    AND state_province <> ?

With a usage like this, DBI allows you to use the execute() statement as:

$sth->execute('Rome', 'New York', 'IT', 'New York');

DBI will substitute the parameters one after another in the same order as it finds ? characters. So the execute() above would return a set of records.

Given the same SQL as above, and this execute statement:

$sth->execute('Rome', 'New York', 'New York', 'IT');

a different set of records would be returned, as this code corresponds to this SQL:

SELECT city FROM locations
WHERE
    (city = 'Rome' or city = 'New York')
    AND country_id <> 'New York'
    AND state_province <> 'IT'

There is nothing wrong with working with just the execute() statement and ? placeholders, but sooner or later you will be forced to mix in-line SQL and parameter SQL, or have an SQL with so many parameters that the SQL statement will be unreadable and confusing.

A statement like this comes to mind.

$sql = 'INSERT INTO table_1
        values(field_1, field_2, field_3, field_4, month)
        select field_1, field_2, field_3, field_4,
        add_months(sysdate, ?) FROM locations
        WHERE
            (city = $city_1 or city = ?)
            AND country_id <> ?
            AND state_province <> ?';

$sth = $dbh->prepare($sql);
$sth->execute($var_1, $var_2, $var_3, $var_4);

Happily, the statement handle has a the bind_param() method so we can write code that strongly binds one parameter to a placeholder. So, given this code:

$sql = 'SELECT city FROM locations
        WHERE
            (city = ? or city = ?)
            AND country_id = ?
            AND state_province <> ?';

$sth = $dbh->prepare($sql);

We can use the bind_param() like this:

$sth->bind_param(1, "Rome");
$sth->bind_param(2, "New York");
$sth->bind_param(3, "US");
$sth->bind_param(4, "New York");

This will make your code much easier to read. DBD::Oracle also allows you to use the colon : placeholder as well. This placeholder lets you use named placeholders, which can make your code very readable. The above code could be written something like this:

$sql = 'SELECT city FROM locations
        WHERE
            (city = :p_city_1 or city = :p_city_2)
            AND country_id = :p_country_id
            AND state_province <> p_state_province';

$sth = $dbh->prepare($sql);

$sth->bind_param(":p_city_1", "Rome");
$sth->bind_param(":p_city_2", "New York");
$sth->bind_param(":p_county_id", "US");
$sth->bind_param(":p_state_province", "New York");

In the case above, I could have used any string or numeric value after the : as long as it matches the value used in the bind_param() method. The use of a prefix such as p_ makes it easier to read the code.

Another feature of the : placeholder is that it can be used to substitute the same value a number of times within your SQL. Given this SQL,

SELECT to_char(add_months(sysdate, 1), 'Month yyyy'),
       add_months(sysdate, 1),
       sysdate
FROM dual

we could use the bind_param() method like

$sql = "SELECT to_char(add_months(sysdate, :p_add_months), 'Month yyyy'),
               add_months(sysdate, :p_add_months),
               sysdate
        FROM dual";

$sth->bind_param(":p_add_months", $months_to_add);

As such, we need only make one call to the bind_param() method to make two substitutions.

4.5.5   Fetching Data

So far we have seen the fetchrow() method used to get at our data -- or "record set", to use the proper RDBMS term -- that has been returned from Oracle. This is of course familiar to all Oracle programmers as it is the similar to a cursor, in that we iterate though the returned records sequentially, processing each as we move along until none are left.

The fetchrow() method works by getting the current row from the record set and binding the values from the record set into specific Perl variables. So given this code:

#!/usr/bin/perl

use DBI;
use strict;
use vars qw($dbh $sth $sql);

my $city = 'Seattle';
$dbh = DBI->connect('dbi:Oracle:', 'hr@localhost/XE', 'hr');
$sql = 'SELECT city, state_province, country_id FROM locations
        WHERE city = :p_city';
$sth = $dbh->prepare($sql);
$sth->bind_param(':p_city', $city);
$sth->execute();

my ($r_city, $r_state) = $sth->fetchrow();

print "My city is $r_city. \n";
print "My state is $r_state.\n";

$sth->finish();

you would get this result:

$ perl test_4.pl
My city is Seattle.
My state is Washington.

Note how in our SQL statement we were also selecting country_id but we did not use it when we fetched the data from the result set. If I changed to code to this:

my ($r_city, $r_state, $r_country) = $sth->fetchrow();

we could then also print out the country value.

Using fetchrow() quickly gets very tedious when you are returning a large number of fields from a database. Fortunately, the statement handle has the fetchrow_array() method which returns the row as an array. It can work the same way as fetchrow() where we name the values to be fetched individually.

my ($r_city, $r_state, $r_country) = $sth->fetchrow_array();

Or we could fetch all the data into a Perl array.

my (@a_row) = $sth->fetchrow_array();

The code to print this would look like this:

print "$a_row[0] \n";
print "$a_row[1]\n";
print "$a_row[2]\n";

(Note that this method always returns a 0--based array.)

As with fetchrow(), you can iterate with fetchrow_array() with a simple Perl while loop. An example of this is in the code below.

#!/usr/bin/perl

use DBI;
use strict;
use vars qw($dbh $sth $sql);

my $country = 'US';
$dbh = DBI->connect('dbi:Oracle:', 'hr@localhost/XE', 'hr');
$sql= 'SELECT city, state_province, country_id FROM locations
       WHERE country_id = :p_country';
$sth = $dbh->prepare($sql);
$sth->bind_param(':p_country', $country);
$sth->execute();

while (my (@a_row) = $sth->fetchrow_array())
{
    print "My city is $a_row[0], my state is $a_row[1], ";
    print "my country is $a_row[2].\n";
}

$sth->finish();

This would output a result like this:

$ perl test_5.pl
My city is Southlake, my state is Texas, my country is US.
My city is South San Francisco, my state is California, my country is US.
My city is South Brunswick, my state is New Jersey, my country is US.
My city is Seattle, my state is Washington, my country is US.

The statement handle also has a number of other methods that fetch single rows, namely fetchrow_arraryref() and fetchrow_hashref(), both of which will be discussed in later in this tutorial.

4.5.6   Atomic and Batch Fetching

One-way row-fetching is suitable for most of the work you may do with DBI, but occasionally you will need to move back and forth through data, or you may need to cut down the number of steps involved in a process. For those requirements, DBI provides two alternative fetching modes: atomic and batch.

#!/usr/bin/perl

use DBI;
use strict;
use vars qw($dbh $ $country);

$country = "US";
$dbh = DBI->connect('dbi:Oracle:', 'hr@localhost/XE', 'hr');

my ($r_country_id, $r_country_name) =
    $dbh->selectrow_array("SELECT country_id, country_name
                           FROM countries
                           WHERE country_id = '$country'");

print "My country_id is $r_country_id,
       my country name is $r_country_name.\n";

#!/usr/bin/perl

use DBI;
use strict;
use vars qw($dbh $sth );

$dbh = DBI->connect('dbi:Oracle:', 'hr@localhost/XE', 'hr');

my $countries =
    $dbh->selectall_arrayref("SELECT country_id, country_name
                              FROM countries
                              ORDER BY country_name");

foreach my $row (@$countries)
{
    print "My country_id is $row->[0], ";
    print "my country name is $row->[1].\n";
}

foreach my $row (@$countries)
{
    print "My country’s name  is $row->[1], ";
    print "my country’s Id is $row->[0].\n";
}

$sth = $dbh->prepare("SELECT country_id, country_name
                      FROM countries
                      ORDER BY country_name");
$sth->execute();
my $countries = $sth->fetchall_arrayref();

SELECT Employee_Id, First_Name,
       Last_Name, Email, Phone_Number,
       Hire_Date, Job_Id, Salary, Commission_Pct,
       Manager_Id,  Department_Id
FROM employees

my $employees = $sth->fetchall_arrayref([0,2,1,5]);

$sth = $dbh->prepare("SELECT Employee_Id ,First_Name,
                             Last_Name, Email, Phone_Number,
                             Hire_Date, Job_Id, Salary, Commission_Pct,
                             Manager_Id,  Department_Id
                      FROM employees");
$sth->execute();
my $employees = $sth->fetchall_arrayref([0,2,1,5]);

foreach my $row (@$employees)
{
    print "My employee id is $row->[0], my name is $row->[1], ";
    print "$row->[2] and I started on $row->[3].\n";
}

my $employees = $sth->fetchall_arrayref({
                  employee_id=>1,
                  hire_date=>1,
                  first_name=>1,
                  last_name=>1,});

foreach my $row (@$employees)
{
    print "My employee id is $row->{employee_id}, ";
    print "my name is $row->{last_name}, $row->{first_name} ";
    print "and I started on $row->{hire_date}.\n";
}

4.6.1   PL/SQL Procedures and Functions

Like all good database APIs, DBI allows you to call PL/SQL programs that you might have written in the past. DBI allows you not only to call them, but also get the data retrieved by them.

Calling a procedure is quite straight-forward -- you just wrap it between the BEGIN and END PL/SQL keywords.

$sth = $dbh->prepare("BEGIN fire_employee(:p_employee_id); END;");
$sth->bind_param(':p_employee_id', 101);
$sth->execute();

In this case, there is only one input parameter in the procedure, so we need only bind our value to it.

When there is a value returned by the procedure, you will have to the statement handle’s bind_param_inout() method to get the returned value. Let us say our procedure has one input param, (employee_id), and one output param, (hire_date). We would call the stored procedure like this:

$in_employee_id = 101;
my $hire_date;
$sth = $dbh->prepare("BEGIN get_startdate(:p_employee_id, :p_start_date);
                      END;");

$sth->bind_param(':p_employee_id', 101);
$sth->bind_param_inout("", \$hire_date, "SQL_NUMERIC");
$sth->execute();

When this code runs, the $hire_date variable will automatically be updated to the value returned by the procedure, because we bound it using the bind_param_inout() method.

Calling a PL/SQL function with DBI is similar to calling it in PL/SQL -- a placeholder is used to store the value returned from the function, and then the bind_param_inout() method is used get that value into your Perl program.

So the Perl code to get the value for a PL/SQL function that returns the employee_id for the top seller would look like this:

my $big_seller;
$sth = $dbh->prepare("BEGIN :p_big_seller := get_top_seller(); END;");
$sth->bind_param_inout("", \$big_seller, "SQL_NUMERIC");
$sth->execute();
print "The id of the top seller is $big_seller.";

You might have noticed that in both of these examples, SQL_NUMERIC was added to each of the bind_param_inout() calls. This is because Perl is a loosely-typed language, so we have to explicitly tell Perl -- and the DBD::Oracle driver as well -- what type of variable is being passed to Oracle from Perl, and from Oracle and back into Perl.

4.6.2   PL/SQL CURSORS

DBD::Oracle is able to use PL/SQL cursors -- both those that have been generated from functions and those generated directly . The key thing to remember when using cursors is that they have to be bound to a specific DBD::Oracle datatype constant called an ORA_RSET. This constant and the other Oracle-specific ones have to be explicitly imported into your program by the following command.

use DBD::Oracle qw(:ora_types);

With the constants loaded, you can then pass ORA_RSET datatype when binding parameters. Alternatively you could just enter the numeric value as well, but this is very poor programming style.

To get a cursor into a statement handle you would start with this code:

use DBI;
use DBD::Oracle qw(:ora_types);
use strict;

Then, of course, come our variables and connection.

use vars qw($dbh $sth1 $sth2);
$dbh = DBI->connect('dbi:Oracle:', 'hr@localhost/XE', 'hr');

Then we create a statement handle with SQL that will return a cursor.

$sth1 = $dbh->prepare(q{
      BEGIN OPEN :p_cursor FOR
          SELECT first_name, last_name
          FROM employees ORDER BY last_name;
      END;
});

Next, bind :p_cursor to variable reference \$sth2 and set its size to 0. Then tell DBD::Oracle that it is a ORA_RSET or cursor.

$sth1->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET });

We then just fire the execute() method of the first statement handle, which will load the returned cursor into a second statement handle that DBD::Oracle will create. Then we can then simply iterate though the second handle.

$sth1->execute();

while (my @row = $sth2->fetchrow_array)
{
    print "My name is $row[0] $row[1].\n";
}

Here is all the code.

#!/usr/bin/perl

use DBI;
use DBD::Oracle qw(:ora_types);
use strict;
use vars qw($dbh $sth1 $sth2);

$dbh = DBI->connect('dbi:Oracle:', 'hr@localhost/XE', 'hr');

$sth1 = $dbh->prepare(q{
      BEGIN OPEN :cursor FOR
          SELECT first_name, last_name
          FROM employees ORDER BY last_name;
      END;
});

$sth1->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET } );
$sth1->execute();

while (my @row = $sth2->fetchrow_array)
{
    print "My name is $row[0] $row[1].\n";
}

This code will also work if you were calling a function or procedure that returns a cursor, provided you use the Oracle cursor type SYS_REFCURSOR.