DBI Class Methods

Before we connect to Oracle, we must establish a few DBI variable-naming conventions (listed in Table B-1).

Table B-1. Conventional Perl DBI variable names

Name	Description
$dbh	The database handle created on database connection.
$sth	The SQL statement handle.
$drh	The driver handle, mostly used internally by the Perl DBI package.
$h	Can represent any of the three main handles above.
$rc	A general DBI return code, mostly used in a Boolean context.
$rv	A general DBI return value, often used numerically.
@ary	A list of returned scalars, or a row fetched from the database.
$rows	The number of rows processed.
$fh	A file handle, often used to change any default output from STDOUT.
undef	Perl's generic undefined value is used in DBI for NULLs.
%attr	A general name for hashes used to store various attributes.

connect

The connect Perl DBI constructor method generates our main database handle, $dbh:

use DBI;
my $data_source = "dbi:Oracle:orcl";
my $user = "scott";
my $password = "tiger";
my %attr = (RaiseError => 0, AutoCommit => 1); 
my $dbh = DBI->connect($data_source, $username, $password, \%attr) 
          or die $DBI::errstr;

Note the following characteristics of the connection string held above in $data_source:

• $data_source is composed of three elements separated by colons. However if $data_source is undefined, the connect method will replace it with the environmental variable DBI_DSN, making the following possible:

  DBI->connect(undef, $username, $password)

  This becomes interpreted as:

  DBI->connect($ENV{DBI_DSN}, $username, $password)

• Sometimes the "Oracle" driver part of the connection string may be missing, as in:

  DBI->connect("dbi::orcl", $username, $password)

  In this case, the environmental variable DBI_DRIVER is assumed, as if the code actually looked like this:

  DBI->connect("dbi:$ENV{DBI_DRIVER}:orcl", $username, $password)

• Sometimes, the actual target database string, such as orcl, may be missing:

  DBI->connect("dbi:Oracle:", $username, $password)

  In this case TWO_TASK, or subsequently ORACLE_SID, is assumed:

  DBI->connect("dbi:Oracle:$ENV{TWO_TASK}", $username, $password)

Looking inside the $dbh variable

Assuming that everything goes well, we should now have a valid database handle stored in the $dbh variable. But what's actually inside this? Let's find out:

my $dbh = DBI->connect('dbi:Oracle:orcl', 'scott', 'tiger');
print "dbh >", $dbh, "<\n";

Blessed references give us both the class label and an object reference:

dbh >DBI::db=HASH(0x466cd40)<

What we have in $dbh is the key to a DBI::db object. However, Perl DBI is unusual in Perl. It operates within a hierarchy of objects rather than just one. As well as having DBI::db objects, we later hang SQL statement objects off these objects (like baubles from a Christmas tree). Each database handle gets its own collection of statement handles. This hierarchy can be seen in Figure B-1.

Figure B-1. Database handles and statement handles

Each of these handles can also be assigned its own collection of initial and modifiable attributes. Let's see that connection code again:

my %attr = (RaiseError => 0, AutoCommit => 1); 
$dbh = DBI->connect($data_source, $username, $password, \%attr );

You'll often see variations on this theme, with anonymous hashes used instead:

$dbh = DBI->connect($data_source, $username, $password, 
                    {RaiseError => 0, AutoCommit => 1} ); # Anon. Hash

We cover the main generic handle attributes in Table B-2 (many of these are read-only) and the database handle specific attributes in Table B-3. Reading and occasionally resetting these attributes is straightforward:

$old_value = $h->{AttributeName};         # Reading
$h->{AttributeName} = $some_new_value;    # Setting

Table B-2. Main generic handle attributes

Attribute	Description
PrintError	Forces errors to generate warnings. Default is on.
RaiseError	Forces errors to make the program die. PrintError runs before RaiseError, if both are on. Default is off.
Warn	Enabled by default to generate useful warnings.
ShowErrorStatement	Appends DBI statement text to the end of other error messages usually generated by the database.
Kids	For driver handles Kids is the current number of related database handles. For database handles it's the number of associated statement handles.
CachedKids	For a driver handle, references database connections created by connect_cached. For database handles, this references prepare_cached statements.
Taint	If switched on, all "fetched" data is tainted if Perl is in taint mode and method arguments are checked for taintedness.
LongReadLen	Controls the maximum length of long fields such as the various LOBs (large objects). The default LongReadLen value of 80 returns undef for all long fields.
LongTruncOk	If any long data exceeds the LongReadLen value, the fetch will fail. If set to true, the long data is truncated appropriately. Default is off.
FetchHashKeyName	Used with the fetchrow_hashref method and defaulted to NAME, which may return column names in a mixture of upper and lowercase. Set to NAME_uc or NAME_lc to force uppercase or lowercase, respectively.
ChopBlanks	For fixed-width fields, controls blanks trimming.

Table B-3. Main database handle attributes

Attribute	Description
AutoCommit	Automatically commits DML statements when set to true. Defaults to true in order to line up with JDBC and ODBC standards. Robust transactions in production code should switch this attribute off and use the eval operator, which fills the $@ variable with relevant information if RaiseError throws the eval statement (if switched on; see earlier). This behavior is used to create try-catch[1] structures: $dbh->{AutoCommit} = 0; # Turn off! :-) $dbh->{RaiseError} = 1; # Turn on! 8-) eval { # try do_some_stuff( ); do_some_other_stuff( ); $dbh->commit; }; if ($@) { # catch warn "Transaction failed: $@"; $dbh->rollback; do_some_other_cleanup_stuff( ); }
Driver	Holds the parent driver's handle. Useful for finding the name of the driver on a multi-driver system: print $dbh->{Driver}->{Name}, "\n";
Name	Holds the TNSNAME of the database, where TNSNAME is part of the connection string, dbi:Oracle:TNSNAME.
Statement	Holds the latest prepared or executed statement string.
RowCacheSize	A driver hint for row cache sizes for SELECT statements. Very useful for speeding up DBI.

^[1] See Pete Jordan's Exception.pm module for more explicit try-catch structures: http://www.cpan.org/authors/id/P/PJ/PJORDAN/

Alternative Oracle connection scenarios

There are several alternatives for connecting to Oracle. You can use the first alternative, shown in the following example, if you don't have access to a tnsnames.ora file:

$dbh = DBI->connect("dbi:Oracle:host=myhost.com;sid=orcl", 
                    $username, $password);

You can specify the port number in the connection, as shown in the next example. If you don't specify the port number, DBD::Oracle will try ports 1526 and 1521 in that order. Other variations, which are particularly appropriate for older SQL*Net systems, can be used if TWO_TASK or ORACLE_SID have not been set:

$dbh = DBI->connect('dbi:Oracle:T:Machine:sid','username','password');
$dbh = DBI->connect('dbi:Oracle:','username@T:Machine:sid','password');
$dbh = DBI->connect('dbi:Oracle:','username@orcl','password');
$dbh = DBI->connect('dbi:Oracle:orcl','username','password');
$dbh = DBI->connect('dbi:Oracle:orcl','username/password','');
$dbh = DBI->connect('dbi:Oracle:host=foobar;sid=orcl;port=1521', 
                    'scott/tiger', '');
$dbh = DBI->connect('dbi:Oracle:', 
q{scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)
(PORT=1521))(CONNECT_DATA=(SID=orcl)))}, "");

Oracle-specific connection attributes

You can select three connection attributes especially for Oracle:

ora_session_mode

Used to connect with SYSDBA or SYSOPER authorization:

DBI->connect($data_source, $username, $password, 
             { ora_session_mode => 2 }); # SYSDBA
  
DBI->connect($data_source, $username, $password, 
             { ora_session_mode => 4 }); # SYSOPER

ora_oratab_orahome

Set this attribute to true when you are using a DBD::Oracle version built against Oracle7. Doing so changes $ENV{ORACLE_HOME} to the Oracle home directory specified in /etc/oratab, if the database is listed there:

DBI->connect($data_source, $username, $password, 
             { ora_oratab_orahome => 1 }); # True

ora_module_name

Passed to the SET_MODULE function in the DBMS_APPLICATION_INFO package, which identifies this calling Perl application for monitoring and performance tuning purposes. In the following example, $0 is the built-in Perl scalar variable holding the name of the Perl script.:

DBI->connect($dsn, $user, $passwd, { ora_module_name => $0 } );

connect_cached

The connect_cached method is virtually identical in appearance to connect, described in the previous section:

$dbh = DBI->connect_cached($data_source, $username, $password, \%attr);

New database handles are cached. Whenever another call is now made to connect_cached using identical connection parameters, the cached database handle is returned if it is still available. If the handle is not available, a new one is created, as with connect.

available_drivers

The available_drivers method lets us know which DBD drivers (such as DBD::Oracle) are available on the system:

@ary = DBI->available_drivers;

data_sources

The data_sources method lists the available database targets. This method is useful for populating drop-down CGI or Perl/Tk boxes to choose a target database before connection. If no `Oracle' parameter is supplied, the environmental variable DBI_DRIVER is assumed:

@ary = DBI->data_sources('Oracle');

(DBD::Oracle reads oratab and tnsnames.ora to get this information.)

trace

The trace method lets you set the desired debug trace level. Various debug trace levels (shown in Table B-4) are possible under DBI. The default is to turn off tracing.

Table B-4. Tracing levels

Level	Description
0	Tracing is disabled.
1	Useful for overviews.
2	For more serious debug work.
3, 4, 5...	Ever more complex detail for hard-core developers.

Typical calls might look like this:

DBI->trace(0);                      # Turn tracing off.
DBI->trace(1);                      # Turn tracing on, STDERR output.
DBI->trace(2, "my_trace_file.txt"); # Increase trace level, and
                                    # redirect to named file.

You can use another method in conjunction with trace for your own debug messages, as shown here: DBI->trace_msg( $message ) ; DBI->trace_msg( $message, $min_level ) ; If the trace level is greater than 0, this will write $message to either STDERR or any other nominated trace file, or you can specify the minimum level it should report on.

For further trace ability, Perl DBI also holds the very latest handle information in the following handles:

$DBI::err:

Holds the Oracle error code from the last method called.

$DBI::errstr:

Returns the latest Oracle error message.

Let's test this by setting up a piece of code we know will go wrong:

$sth = $dbh->prepare('SELECT Should_go_wrong" from dual'); # Quote! :)
  
print "DBI::err:    >", $DBI::err, "<\n";
print "DBI::errstr: >", $DBI::errstr, "<\n";

This produces the following output:

DBI->err:    >1740<
DBI->errstr: >ORA-01740: missing double quote in identifier 
(DBD ERROR: OCIStmtPrepare)<