/ 
extproc_perl-0.95
River stage zero No dependents
/ extproc_perl

NAME

extproc_perl - Oracle Perl Procedure Library

SYNOPSIS

FUNCTION SYNTAX

    select perl('sub1','1','2') from dual;

PROCEDURE SYNTAX

    exec perl('sub2','arg1');

DESCRIPTION

extproc_perl enables Oracle stored procedures and functions to be written in Perl. It uses Oracle 8's external procedure interface to create Perl interpreters on the fly to execute Perl subroutines. Code is stored in a central "bootstrap" file that is interpreted after each interpreter is initialized. In the current implementation of Oracle external procedures, the Perl interpreter is persistent for the life of a client session. This has the effect of keeping both interpreted code and data in memory for the life of that particular session.

MODULES

The Oracle external procedure process and DynaLoader do not work well together. In fact, DynaLoader doesn't work at all in this environment. Therefore, in order to use dynamically loaded modules like Socket, hooks for each module need to be compiled into the extproc_perl shared library. This means that for each module you want to use, you must declare it at build time. The Makefile.PL file provides gives you the option of declaring these modules. Note that this restriction does not apply to Perl-only modules, but it's safe to declare them as well during this process -- they will be ignored.

ExtProc is a module that is statically linked into the extproc_perl object. It provides several functions that interact with the Oracle database itself. See the ExtProc(3) manpage for details.

SPECIAL SUBROUTINES

The following subroutines are reserved for extproc_perl, and can be called like any other Perl subroutine from Oracle.

_flush()

Destroys Perl interpreter and all Perl data. A new interpreter will be started for the next query.

_version()

Reports the version of the currently loaded extproc_perl.

_modules()

Returns a space separated list of modules that were requested to be statically linked into extproc_perl.

_codetable(table_name)

Sets or returns the database table queried for code.

_error()

Returns the most recent error string.

_preload()

Force extproc_perl to load code from the database immediately.

DBI CALLBACKS

If you are using DBD::Oracle with the supplied patch, you can query the calling database using DBI. This is a surprisingly fast operation, as there is no need to establish a new session to the database. There are three basic steps to use a DBI callback:

    1. Fetch the OCI context using the ExtProc module as follows:

      use ExtProc; my $context = ExtProc::context;

    2. Initialize the callback infrastructure using DBI->connect. Connect to the database "extproc" and pass the context from step 1 as the attribute "ora_context":

      my $dbh = DBI->connect("dbi:Oracle:extproc", "", "", { 'ora_context' => $context } );

    3. Query/Update the database using standard DBI methods.

NOTE: External procedure callbacks are stateless, therefore you MUST call DBI->connect before EACH callback to the database to reestablish the callback infrastructure.

The following rules apply when using DBI callbacks:

  • * Queries work from both functions and procedures.

  • * DML statements (insert, update, delete, etc.) only work from procedures. This is an Oracle restriction.

  • * DDL statements (create table, drop table, etc.) will not work at all. Again, an Oracle restriction.

  • * Due to a bug in the passing of contexts between extproc_perl and DBI, you MUST call the special _preload subroutine under the following conditions:

    • * You are calling the subroutine as a procedure.

    • * The procedure to be called is stored in the database.

    • * The procedure uses DBI to modify the database (queries do not appear to be affected by this bug, but your mileage may vary).

    • * The procedure is the first subroutine to be called in the session that is stored in the database. If a previous subroutine has been called that was stored in the database, it is not necessary to call _preload.

    These rules are necessary becuase there appears to be some mishandling of the OCI context between the internal callback to fetch the database code and the callback in the procedure. It's a bug for sure, but _preload works around it for now. If this really bothers you, store your DML code in the bootstrap file. There is no ETA on a fix for this bug.

AUTHOR

Jeff Horwitz <jeff@smashing.org>

SEE ALSO

perl(1), perlembed(1), ExtProc(3)