ExtProc - Perl interface to the Oracle Perl External Procedure Library

  use ExtProc;

  my $e = ExtProc->new;
  my $dbh = $e->dbi_connect;
  $e->ora_exception("error");

The ExtProc module provides functions for interacting with the calling Oracle database from extproc_perl scripts.

put_line(string)

Uses DBMS_OUTPUT.put_line to return output to the calling program (usually sqlplus). You must have enabled output from stored procedures ("set serveroutput" in sqlplus). You can also use the filehandle interface; see "fh" for details.

ep_debug(message)

If debugging is enabled, write the specified message to the debug log.

ora_exception(message)

Throws a user-defined Oracle exception. Note that the Perl subroutine will probably complete after this function is called, but no return values should be accepted by the calling client.

is_function()

Returns true if the subroutine is being called as a function.

is_procedure()

Returns true if the subroutine is being called as a procedure.

new()

Returns an ExtProc object that can be used to call the methods below.

dbi_connect(\%attr)

Obtain a DBI handle for the calling database, with optional DBI attributes.

 use DBI;
 use ExtProc;

 # get ExtProc object
 my $e = ExtProc->new;

 # connect back to the calling database
 my $dbh = $e->dbi_connect();

 # raise errors
 my $dbh = $e->dbi_connect({RaiseError => 1});

NOTE: External procedures are stateless, so there is no concept of a persistent connection to the database. Therefore, you must call dbi_connect once per transaction.

fh()

Returns a filehandle that can be used for returning output to PL/SQL. You must have enabled output from stored procedures ("set serveroutput" in sqlplus).

 use ExtProc;

 sub testprint
 {
    my $e = ExtProc->new;
    my $fh = $e->fh;
    print $fh "Hello world!";
 }

 SQL> set serveroutput on
 SQL> exec Perl.proc('testprint')
 Hello world!

register_destructor(coderef)

Register a destructor with extproc_perl to be called before the session exits. coderef should be a reference to a named or anonymous subroutine. Destructors are pushed onto a stack, and will be called in LIFO (last in, first out) order.

Destructors MUST exist prior to registration. Since no context exists between the database and external procedure during module unload, no attempt will be made to fetch the code from the database. In fact, destructors cannot access the database at all.

 use ExtProc;

 sub bye
 {
    do_something();
 }

 my $e = ExtProc->new;
 $e->register_destructor(\&bye);

Oracle DATE values are passed to Perl as objects of type ExtProc::DataType::OCIDate. You can manipulate the date and time values within by using the methods documented below.

new()

Creates a new NULL date object. Use as follows:

use ExtProc;

$date = ExtProc::DataType::OCIDate->new;

setdate_sysdate()

Set the DATE to the current system date & time.

is_null()

Returns true if the DATE is NULL, false otherwise.

set_null()

Set the DATE to an Oracle NULL. Never "undef" an Oracle DATE type.

getdate()

Returns a list of the year, month, and day of the date.

 ($year, $month, $day) = $date->getdate;

setdate(year, month, day)

Sets the date in the date object according to the supplied arguments.

gettime()

Returns a list of the hour (24-hour format), minute, and second of the date.

 ($hour, $minute, $second) = $date->gettime;

settime(hour, minute, second)

Sets the time in the date object according to the supplied arguments.

to_char(format)

Perl implementation of the PL/SQL to_char function. Returns a string representation of the date object in the format you specify. See the Oracle documentation for appropriate date formats.

 print $date->to_char('YYYY/MM/DD HH24:MI:SS');

Jeff Horwitz <jeff@smashing.org>

perl(1), perlembed(1), DBI(3), DBD::Oracle(3)