Section 7.5. The Future of Oracle::OCI

7.5 The Future of Oracle::OCI

We're sure that within a few years Oracle::OCI will be an advanced, mature set of packages used by every Perl Oracle user around. Author Tim Bunce actually predicts that Oracle::OCI will eventually hold the complex code, but that simpler modules will be layered on top to provide specific functionality. For example, future modules may include the following:

Oracle::LOB

For dealing with large binary objects

Oracle::DirectPath

For speeding data loads

Oracle::Collection

For collections, index-by tables (associative arrays), and nested tables

Oracle::Transaction

For finely grained remote transactions

7.5.1 Oracle::PLSQL

Another extremely interesting development in the world of OCI will be Oracle::PLSQL . At the time we were writing this book, this module was planned but did not yet exist. We expect this module to provide fantastic connectivity between PL/SQL and Perl. In this way, it will tie in neatly with the extproc_plsql module we describe in Chapter 8. Using the upcoming Oracle::PLSQL , PL/SQL functions could be mapped directly to Perl functions, and vice versa, in a way similar to what's happened with SQLJ and Java. We expect that eventually there could be room enough for this symbiosis of Perl and PL/SQL to manage all of the following Oracle features:

• Transparent use of the UTL_FILE built-in package between PL/SQL and Perl

• Advanced Queuing

• Replication

• Standby databases

• Parallel servers

• Gathering and processing of performance statistics

• Building of custom tools with the DBMS_DEBUG built-in package

For instance, Tim Bunce predicts snippets of code, such as the following example, which works with binary files to load their information into the $buffer variable:

 use DBI; $dbh = DBI->connect('dbi:Oracle:', $user, $pass, { ora_autolob => 0 }); $bfile =     $dbh->selectcol_array(       "select bfile from mylobs where id=? for update", undef, 1);    use Oracle::PLSQL;    $dbms_lob = new Oracle::PLSQL DBMS_LOB => $dbh; $dbms_lob->fileexists($bfile) or die "File missing";    $length = $dbms_lob->filelength($bfile); $dbms_lob->filegetname($bfile, $diename, $filename); $dbms_lob->fileopen($bfile, $dbms_lob->{file_readonly}); $dbms_lob->read($bfile, 40, 1,  $buffer  ); $dbms_lob->fileclose($bfile); 

Stay tuned to the Oracle::OCI dial for further information.

7.5.2 Contributing to the Oracle::OCI Project

Most Oracle DBAs probably already have their hands full and won't be interested in adding to their workload. But if you do want to help with the Oracle::OCI project, we recommend that you get hold of the latest download and start trying to make it work for your own needs. It can take a while to get the hang of it, but for practice you might want to try to replicate the OCI demonstration programs provided by Oracle Corporation, listed in the next section. For further information on the project, visit Tim Bunce's CPAN site where you'll find numerous presentation downloads on DBI, DBD::Oracle , and Oracle::OCI :

http://www.perl.com/CPAN/authors/id/TIMB

7.5.3 Demo Programs

If you want to cut your teeth on some hard- core OCI programming, check out the code examples provided by Oracle Corporation detailing in-depth usage of OCI calls, available automatically within your current Oracle installation. ^[6] These C files, and supporting SQL files, generally appear in the ../rdbms/demo or ../oci/samples directories. A variety of helpful information is included within the header parts of these files; for example, you'll find out what accompanying SQL files need to be preinstalled , and so on. We've listed the OCI 8.1 demonstration programs in Table 7-5.

^[6] These are not guaranteed to appear, nor guaranteed to work, by Oracle Corporation, but you'll generally find them and they do generally work. They provide excellent templates on which to build your own code, especially if you wish to help develop Oracle::OCI.

Table 7-5. OCI demonstrations from Oracle Corporation

C programs	Description
cdemo1.c..cdemo5.c	Basic SQL processing
cdemo81.c, cdemo82.c	Basic Oracle8 SQL session and object processing
cdemobj.c	REF selection and navigational interface
cdemocor.c..cdemocor1.c	Demonstrate a prefetching user interface
cdemodp.c..cdemodp_lip.c	Loading data through direct path API
cdemodr1.c..cdemodr3	Returning values and LOBs, etc.
cdemodsa.c, cdemodsc.c	Used for describing tables
cdemoext.c	OCI extraction
cdemofil.c	OCI file handling
cdemofo.c	OCI callbacks for application failover
cdemofor.c	OCI formatting
cdemolb2.c, cdemolb2.c, cdemolbs.c, cdemoplb.c	Working with LOBs
cdemorid.c	DML prefetches
cdemort.c	Type information
cdemoses.c	Session management
cdemosyev.c	System event registration
cdemothr.c	OCI threading
cdemoucb.c, cdemoucbl.c	User callbacks