1.2 The Perl/Oracle Architecture

How do Oracle DBAs, developers, and users take advantage of everything that Perl has to offer? The architecture illustrated in the figures in the following sections show how the various Perl and Oracle modules fit together to make the Perl/Oracle connection clean and efficient. In the following sections we'll take a look at the main components of this architecture:

• Perl DBI

• DBD::Oracle

• OCI

Perl DBI and DBD::Oracle are Perl modules available from CPAN. OCI is an Oracle Corporation product that comes with all versions of the Oracle database.

1.2.1 Perl DBI and DBD::Oracle

Perl DBI is a generic application programming interface (API). It is similar in concept to ODBC (Oracle DataBase Connectivity) and JDBC (Java DataBase Connectivity), but it has a Perl-based object-oriented architecture. Perl DBI's object-oriented architecture allows it to have a single routing point to many different databases (shown in Figure 1-1), each via a database-specific driver. Oracle uses the DBD::Oracle driver, another Perl module that provides the actual communication to the low-level OCI code. It is OCI that makes the final connection to the Oracle database.

Figure 1-1. Perl DBI can interface to many databases

The beauty of Perl DBI is you can forget the details of the necessary connections beneath its simple API calls. The DBI package glides serenely over the surface of our databases, while the driver module, DBD::Oracle, does all the hard paddling beneath the surface.

Figure 1-2 shows how all the modules fit together on the Perl and Oracle sides.

Figure 1-2. The Perl/Oracle architecture

1.2.1.1 The origins of Perl DBI

The origins of Perl DBI date back more than a decade. Way back in 1991, an Oracle DBA, Kevin Stock, created a database connection program called OraPerl that was released for Perl 4. Over time, similar Perl 4 programs appeared, such as Michael Peppler's Sybperl, designed for communication with the Sybase database. In a parallel development, starting around September of 1992, a Perl-based group was working on a specification for DBPerl, a database-independent specification for Perl 4. Within two years they were just ready to start implementing DBPerl when Larry Wall started releasing the alpha version of the object-oriented Perl 5. Taking advantage of both Perl 5 and the earlier Call Level Interface (CLI) work from the SQL Access Group, the DBPerl team relaid the foundations of Perl DBI within an object-oriented framework, creating this new architecture in a similar form to that employed by the familiar API of ODBC. Meanwhile, Tim Bunce wrote an emulation layer for OraPerl Version 2.4 that let people easily move their legacy Perl 4 OraPerl scripts over to Perl 5 and Perl DBI.

With the new DBI architecture, you could now transparently employ just one Perl module to connect to every type of database, as long as you had the right driver. Fortunately for Oracle DBAs, Tim Bunce, the main creator of Perl DBI, is also the main creator of DBD::Oracle, which automatically keeps Oracle on the cutting edge of Perl DBI's development schedule.

1.2.1.2 The Perl DBI API

We won't try to describe all of the capabilities of Perl DBI here, but Table 1-1 provides a summary of the main calls (e.g., DBI class methods) to OCI. For additional background information about Perl DBI, see Appendix B. And for much more information, consult the references listed under Section 1.4.2 at the end of this chapter.

Table 1-1. Main Perl DBI functions

DBI function	Description
available_drivers( )	Lists all of the available DBD drivers including DBD::Oracle
data_sources( )	Lists all of the databases available to DBD::Oracle
connect( )	Establishes an Oracle database connection
disconnect( )	Disconnects a login session from Oracle
err( )	Returns the relevant Oracle error code
errstr( )	Supplies an associated Oracle error message
prepare( )	Prepares a SQL statement for execution
execute( )	Executes a prepared statement
do( )	Prepares and executes a single SQL statement all together
bind_param( )	Binds a value to a prepared statement
commit( )	Commits a transaction
rollback( )	Rolls back a transaction
table_info( )	Fetches metadata information from a table
fetchrow_arrayref( )	Fetches a row of data into a referenced array
fetchrow_array( )	Fetches a row of data into an array
selectrow_array( )	Executes prepare( ), execute( ) and fetchrow_array( ) all in one call

1.2.2 The Oracle Call Interface

As we've said, Oracle Corporation's Oracle Call Interface (OCI) is the component in the Perl/Oracle architecture that makes the final connection to the Oracle database servers. This C-based API provides a comprehensive library used to connect into Oracle from the external world. Use of OCI lets your Perl programs take advantage of the following OCI capabilities:

• High performance

• Security features, including user authentication

• Scalability

• N-tiered authentication

• Full and dynamic access to Oracle objects

• User session handles

• Multi-threaded capabilities

• Support for accessing special Oracle datatypes such as LOBs (large objects)

• Transactions

• Dynamic connection and session management

• Asynchronous event notification

• Access to other databases

• Full character set support

For more about OCI, see Chapter 7, where we describe Oracle::OCI, a new Perl module that provides an even closer interface between Perl and Oracle. You can get complete information about OCI at Oracle Corporation's http://technet.oracle.com pages; in particular, see http://technet.oracle.com/tech/oci/.

In Table 1-2 we list the main OCI functions to give you a sense of the kinds of Oracle operations you can invoke from your Perl programs.

Table 1-2. Main OCI functions

OCI function	Description
OCIAttrSet( )	Sets handle attributes
OCIAttrGet( )	Gets attributes from a handle
OCIBindByName( )	Links variables to a SQL statement placeholder by name
OCIBindByPos( )	Links variables to a SQL statement placeholder by position
OCIDefineByPos( )	Links a typed select-list item with the output data buffer
OCIDescribeAny( )	Describes schema objects
OCIDescriptorAlloc( )	Allocates storage for descriptors and LOB locators
OCIDescriptorFree( )	Releases the resources taken by descriptors
OCIEnvInit( )	Allocates the initial OCI environment handle
OCIErrorGet( )	Returns a buffered error message
OCIHandleAlloc( )	Points to an allocated handle
OCIHandleFree( )	Explicitly releases a memory handle and its resources
OCIInitialize( )	Initializes the environment for OCI processes
OCILobRead( )	Reads specified LOB and FILE portions into a buffer
OCILobWrite( )	Writes a specified buffer into a LOB
OCILogoff( )	Ends a login session
OCILogon( )	Logs into the OCI session
OCIParamGet( )	Gets the descriptor of a parameter attached to a statement handle
OCIParamSet( )	Puts the object retrieval descriptor into an object retrieval handle
OCIServerAttach( )	Creates the pathway to a data source
OCIServerDetach( )	Detaches from a data source
OCISessionBegin( )	Begins a user session for a given server
OCISessionEnd( )	Ends a user session
OCIStmtExecute( )	Sends an application request to the server
OCIStmtFetch( )	Fetches data rows from previous queries
OCIStmtPrepare( )	Prepares a SQL statement for later execution
OCITransCommit( )	Commits a nominated transaction

At the most basic level, virtually all outside programs — from web applications to standalone GUI applications — interact with Oracle through this OCI program layer. Fortunately, the OCI libraries are automatically available in every Oracle database installation, so no special installation process is required. You'll generally discover the appropriate files under the $ORACLE_HOME/lib and $ORACLE_HOME/include directories, on Unix systems, and under %ORACLE_HOME%\lib and %ORACLE_HOME%\include on Win32.