The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.
 / 
extproc_perl-0.97
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.

_errno()

Returns the most recent system error message ($! in Perl).

_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 two basic steps for using a DBI callback:

    1. Initialize the callback infrastructure and obtain a DBI handle using ExtProc->dbi_connect:

      my $dbh = ExtProc->dbi_connect();

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

NOTE: External procedure callbacks are stateless, therefore you MUST call DBI->connect once per transaction (for extproc_perl, this means once per function or procedure call from Oracle).

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.

AUTHOR

Jeff Horwitz <jeff@smashing.org>

SEE ALSO

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