$Id: README.DBI,v 1.7 2003/04/16 14:29:12 jeff Exp $

It may be useful to query the calling database from an external Perl procedure.
Since DBI is the standard Perl module for database access, it makes sense to
use it, along with the DBD::Oracle driver, to connect back to the database.
Support for external procedures is included in DBD::Oracle as of version 1.13,
but a bug prevents it from working in that version and 1.14.  A patch is
supplied to fix that bug.  If you are running DBD::Oracle version 1.12 or
earlier, you can apply another patch which adds external procedure support.

Here's how to apply the patches:

1) Change to the DBD-Oracle distribution directory
2) Run: chmod 644 dbdimp.c
3) Patch DBD::Oracle as follows:

   If you have DBD::Oracle 1.12 or earlier:

    1) Copy DBD-Oracle.patch from the extproc_perl distribution to the current
       directory.
    2) Run: patch -p1 < DBD-Oracle.patch

   If you have DBD::Oracle 1.13 or 1.14:

    1) Copy DBD-Oracle-1.13.patch from the extproc_perl distribution to the
       current directory.
    2) Run: patch < DBD-Oracle-1.14.patch

4) Rebuild and install DBD::Oracle
5) Rebuild extproc_perl and include DBI and DBD::Oracle in the list of modules
   to link with.

To use DBI to connect back to the calling database, you need the current
OCIExtProcContext object.  What, you say?  Don't worry -- the ExtProc module
and DBD::Oracle take care of this for you.  Retrieve the context using the
ExtProc::context method, and pass it to the DBI->connect method as an
attribute.  No need to pass a username or password, as you are technically
already connected to the database as the user who called the external
procedure!  Just set the database name to 'extproc', NOT the name of the
database (or you'll connect normally, incurring a LOT of overhead).

The example below is from the ExtProc module documentation:

 use DBI;
 use ExtProc;

 # get the current OCI context
 my $context = ExtProc::context;

 # connect back to the calling database
 my $dbh = DBI->connect("dbi:Oracle:extproc", "", "",
            { 'ora_context' => $context });

If you are connecting to another database, one which did NOT call the external
procedure, just use DBI as you normally would.

IMPORTANT: External procedures are NOT stateful, which means that there is
no concept of a "connection" back to the calling database.  Therefore, you
need to call DBI->connect EACH TIME you want to make a query.  Don't worry,
it's very fast, since Oracle has already set up the infrastructure to make
these queries.  You can call the disconnect method to be proper, but it's
probably not mandatory.

See the extproc_perl POD documentation for important restrictions on using
DBI callbacks.