extproc_perl 2.50 User's Guide

This guide is accurate as of extproc_perl version 2.50.

Table of Contents

Overview
Installation
Configuration
Using Perl Stored Procedures
extproc_perl Views
Utilities
Debugging


Overview

extproc_perl enables you to write Oracle stored procedures in Perl. Before extproc_perl, you were limited to writing stored procedures in PL/SQL, Java, or C. Each of these languages has its strengths and weaknesses, but none offers the ease of use, flexibility, and extensibility of Perl.

A separate Perl interpreter is created for each client session, and each interpreter remains persistent for the life of its session. This allows for one-time code compilation and data persistence across calls within the session.

One of the most prolific Perl modules is DBI, which is the de facto mechanism for accessing databases from Perl. extproc_perl allows you to use standard DBI methods to make queries and updates to the calling database within the existing transaction; there are no usernames, passwords, or SQL*Net overhead to worry about. In fact, you'll find that DBI queries from extproc_perl return much faster than corresponding DBI queries over SQL*Net.

This document is a comprehensive guide to extproc_perl 2.0, and will cover everything from installation to various advanced topics. It assumes familiarity with Oracle and PL/SQL stored procedures.

Installation

Requirements

Downloading extproc_perl

You can download extproc_perl from http://www.smashing.org/extproc_perl or from CPAN at http://www.cpan.org/modules/by-authors/id/JHORWITZ. Due to the tight integration with both Perl and Oracle, you must build extproc_perl from source code; no binaries are provided.

Before Building

Make sure you have the following information before installing extproc_perl: NOTE: You must actually install the Perl modules you specify in the configuration before you build extproc_perl.

Building extproc_perl

Set your ORACLE_HOME environment variable if you have not done so already. Now extract the file you downloaded and begin the configuration as follows:
gunzip -c extproc_perl-2.0.tar.gz | tar xvf -
perl Makefile.PL
You will now be asked several configuration questions. Your answers are cached after each configuration and set as the defaults the next time around. The questions themselves are simple enough and contain enough documentation that they don't warrant repeating here. Once the configuration process is complete, type make to build extproc_perl.

Once the build is complete, you can test your build of extproc_perl. You'll have to make some changes to your environment first:

Type make test to run the tests. Assuming all goes well, become root and type make install to install extproc_perl.

Connect to your database via sqlplus and create the Perl package as follows:

SQL> @ep_package

Library created.

No errors.

Package created.

No errors.

Package body created.

No errors.
Now create the extproc_perl schema:
SQL> @ep_schema

Table created.


View created.


View created.

No errors.

Configuration

Configuring The Listener

The first part to configuring extproc_perl is telling the listener what to run when it receives a request to run a Perl procedure. You will need to edit two files, listener.ora and tnsnames.ora, which are usually found in $ORACLE_HOME/network/admin, but may be located elsewhere on your particular system (see your DBA if you're unsure).

NOTE:: If you have a DBA, have him or her configure the listener for you, as there may be settings or procedures in place that you don't know about.

In tnsnames.ora, add the following lines if they don't already exist:

Oracle 8i:

extproc_connection_data =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = IPC)
      (KEY = callout)
    )
    (CONNECT_DATA =
      (SID = callout)
    )
  )

Oracle 9i:

extproc_connection_data =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = IPC)
      (KEY = callout)
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
    )
  )

In listener.ora, add the following lines if they don't already exist (you may have to merge this with other listener entries):

LISTENER =
  (ADDRESS_LIST=
        (ADDRESS=(PROTOCOL=IPC)(KEY=callout))
  )

SID_LIST_LISTENER=
  (SID_LIST=
        (SID_DESC=
                (SID_NAME=callout)
                (ORACLE_HOME=your_oracle_home_here)
                (PROGRAM=extproc)
        )
  )

Oracle 9i:

LISTENER =
  (ADDRESS_LIST=
        (ADDRESS=(PROTOCOL=IPC)(KEY=callout))
  )

SID_LIST_LISTENER=
  (SID_LIST=
        (SID_DESC=
                (SID_NAME=PLSExtProc)
                (ORACLE_HOME=your_oracle_home_here)
                (PROGRAM=extproc)
        )
  )

If you are running Oracle 9.2.0 or higher, you must explicitly specify any external procedure library that does not reside in $ORACLE_HOME/lib. You can do this by adding the following line to the SID_DESC section of listener.ora:

(ENVS="EXTPROC_DLLS=/path/to/your/build/directory/extproc_perl_test.so")

If there are existing entries in EXTPROC_DLLS, separate them with a colon (:). You can also use ANY instead of the path to extproc_perl.so, but this carries some security risks. When the listener configuration is complete, reload the listener by typing lsnrctl reload.

Configuration File

extproc_perl utilizes a single configuration file called extproc_perl.conf, located in the configuration directory you specified during installation. Changes to this file will only be seen by extproc_perl sessions started after the change was made.

NOTE: Perl.flush will not cause the configuration to be reloaded.

Syntax

The syntax of extproc_perl.conf is simple. Each line contains a setting, a comment, or is blank. Settings are simple key/value pairs, separated by whitespace. Comments begin with a hash (#). Empty settings should be commented out. Here's an example:
# path to bootstrap file
bootstrap_file /var/tmp/boot.pl

# name of the code table
code_table perl_source

# additions to perl's include path (@INC) -- colon separated
#inc_path

# path to debug output directory
debug_directory /tmp

Configuration Directives

bootstrap_file path

Default: from initial configuration
Path to the bootstrap file, which is executed every time an interpreter is started.

code_table table

Default: from initial configuration
Name of the Oracle table where your Perl code is stored. This should be set automatically for you during the installation.

debug_directory path

Default: /tmp
The directory in which to store debugging logs when debugging is enabled. The external procedure user must have permission to write into this directory.

enable_package_subs yes|no

Default: no
Allow package prefixes on subroutine names (e.g. Digest::MD5::md5_hex). This is incredibly useful for development, but it can be insecure as it allows execution of arbitrary subroutines from currently loaded modules.

enable_session_namespace yes|no

Default: yes
Create a separate namespace for each session, into which your Perl code will be compiled.

enable_tainting yes|no

Default: yes
Enable or disable Perl's taint checking mode. It is highly recommended that tainting is enabled in production environments!

inc_path path1:path2:...

Default: blank
Additional paths to search when Perl is asked to load a module (usually via use or require). Paths should be colon (:) separated as follows: /usr/local/lib:/home/jeff/lib:/tmp/lib

NOTE: inc_path is the proper way to set @INC in extproc_perl; use lib in a BEGIN block does not work properly.

reparse_subs yes|no

Default: no
Setting this option to yes will tell extproc_perl to check for changes in the code table before running a procedure. If changes are detected, the code will be reparsed before execution. This is very useful in development environments, but incurs some overhead that you probably don't want in production.

ddl_format standard | package

Default: standard
Specifies the format in which extproc_perl will write the DDL for directly executed procedures. standard format will write a PL/SQL CREATE statement. package format will write the DDL for inclusion in a PL/SQL package.

max_code_size bytes

Default: 32768
Maximum size in bytes of a subroutine that can be imported into the database. This includes any code outside of the subroutine that is contained in the same file. The maximum value of this setting is 4 GB, though setting it that high is not recommended.

max_sub_args number

Default: 32
Maximum number of arguments that can be passed to a Perl subroutine using the generic Perl.func and Perl.proc wrappers. The maximum number is 128.

trusted_code_directory path

Default: from initial configuration
Path to the directory from which Perl code will be imported. C source code used for directly calling Perl subroutines will also be placed here.

Using Perl Modules

One of the advantages of using extproc_perl is that you can take advantage of the thousands of third-party modules available on CPAN. However, while you can install a pure Perl module (written only in Perl) and use it immediately from your stored procedures, you cannot do the same for modules with C components. This is because Perl modules compiled from C are built as shared libraries and are loaded by Perl only when necessary. Oracle doesn't allow this loading to happen. extproc_perl overcomes this limitation by directly linking each shared library to extproc_perl itself. When Oracle loads extproc_perl, the operating system pulls in the linked module libraries along with it before control is handed off to Oracle.

extproc_perl version 1 required you to rebuild and reinstall the entire package when you added or removed modules. Version 2.0 makes this process much simpler; all you have to do is edit a file and run a single command.

The file you need to edit is called perlxsi.mk in the trusted code directory you specified during configuration. Edit the line beginning with MODULES= and add the modules you'd like to use from extproc_perl, and remove the ones you don't need. The Module names should be separated by spaces. Here's an example:

# Edit the MODULES variable to configure the modules available from extproc_perl
MODULES=ExtProc IO Socket attrs Fcntl DBI DBD::Oracle Digest::MD5

One you've made your changes to perlxsi.mk, type make -f perlxsi.mk, which will relink extproc_perl with the new modules.

NOTE: The number of modules you specify here can have a dramatic effect on the startup time of extproc_perl. Therefore, aside from the defaults provided by extproc_perl, you should only link in the modules you actually use in your code.

Using Perl Stored Procedures

Code Storage

Code can be stored in either the bootstrap file or the database.

Bootstrap File

The bootstrap file is a Perl script that is compiled and run each time a new Perl interpreter is created. It can be used to declare subroutines, initialize data, or preload modules for later use. Additionally, since the bootstrap file is located on the local file system of your database server, it can be used to define system-specific data structures for a database running on multiple servers (e.g. cluster or parallel server). In this case, however, remember that each server must have its own copy of the bootstrap file (but they don't need to be identical).

NOTE: The bootstrap file should be placed on a local file system to ensure that it is always available to the local database.

Code Table

Code can also be stored in the database in what is known as the code table. This table is specified at compile time, but can be changed in extproc_perl.conf if it ever needs to be relocated. Code is imported into the code table from files in the trusted code directory. Each imported file is stored in a row in the code table, and can be referenced by the name of one (and only one) of the subroutines in the file. The benefit to storing code in the database is that it travels with the database, not with the hardware. When you back up the database, you back up your procedures. It is also shared between instances of the same database, so there's no need to manually replicate files full of code.

The Perl.import_perl procedure is used to import Perl code. Pass it the name of the subroutine that will be called from Oracle and the path to the file containing the code. The path should be relative to the trusted code directory. The Perl.drop_perl procedure is used to drop a Perl subroutine from the code table. Pass it the name of the subroutine to drop. Here's an example of importing a subroutine called double from the file double.pl in the trusted code directory, and then dropping it:

SQL> exec Perl.import_perl('double','double.pl')

PL/SQL procedure successfully completed.

SQL> exec Perl.drop_perl('double')

PL/SQL procedure successfully completed.
NOTE: You can omit the filename from import_perl if the file has the same name as the procedure it contains, with a .pl extension.

Calling Perl Procedures

Perl stored procedures behave like any other Oracle stored procedure. They can be passed arguments of different types and, in the case of functions, return data. They also support passing data back via arguments, in the form of Oracle's IN OUT and OUT parameters.

In Oracle, there are significant differences between a procedure and a function. Since Perl does not make this distinction, extproc_perl handles the differences for you, depending on how you call your subroutine. For example, since Perl subroutines always return data in some form, extproc_perl discards those return values if you are in a procedural context, but returns the data if a function was called.

extproc_perl also handles type conversions for you, so you don't have to worry about converting a scalar string to a VARCHAR2, for example.

With all of this in mind, you're now ready to actually run some code! There are three ways to execute a Perl stored procedure using extproc_perl:

Perl.func

Perl.func is a generic wrapper that can be used to call any Perl subroutine as a function. It accepts a subroutine name and up to 8 arguments (can be increased to 126) which are converted to VARCHAR2 and passed to your Perl subroutine as scalar arguments. It always returns a VARCHAR2. Functions can be used in PL/SQL procedures or in queries.
insert into accounts (name,password) values('joeuser',Perl.func('encrypt','foobar'));

Perl.proc

Perl.proc is a generic wrapper like Perl.func except that it calls your subroutine as a procedure. Procedures are used for performing specific tasks and are allowed to do things that functions cannot; these will be discussed later in this document.
exec Perl.proc('give_raises','1000')

Both Perl.func and Perl.proc take advantage of the autoconversion "magic" of Perl scalars. For example, strings are converted to numbers when referenced in a numerical context and vice versa. You can pass a number to Perl.func in PL/SQL and can use it in a string in Perl. Likewise, you can return a numerical value from Perl and it will be converted to a string for Oracle to use in the VARCHAR2 return value. This feature is what allows this wrapper to be so generic.

SQL> select Perl.func('subtract',5,3) as result from dual;

RESULT
------
2

However, these wrappers have some significant limitations:

If these shortcomings don't have any impact on your application, you should feel free to use them exclusively; they will always be supported as the "quick and dirty" way to call Perl subroutines.

Direct Execution

Most users need to work with more than VARCHAR2 datatypes. Directly executed procedures throw away the generic wrappers we just discussed and allow the user to create a real Oracle external procedure that is tightly integrated with your subroutine. The result is a procedure that you can call by name and that can accept and return multiple data types. For example, here's the subtract function from the previous section, except we've set it up to be called directly:
SQL> select subtract(5,3) as result from dual;

RESULT
------
2
That's a lot more readable, isn't it? It's also faster and more efficient, though you can't really tell with just a single call. The time savings are really noticeable however, when performing repetetive tasks like creating an index or running an aggregate function.

In general, there are three steps when creating a directly executable procedure:

  1. Create the procedure specification
  2. Import your code
  3. Create the external procedure
Create The Procedure Specification
The specification, or prototype, for your subroutine is the standard PL/SQL procedure definition, which looks something like this:
FUNCTION|PROCEDURE name [(arg1 IN|IN OUT|OUT datatype, [...],)] [RETURN datatype]
A function called foo that takes one VARCHAR2 argument and returns a VARCHAR2 would be specified as follows:
FUNCTION foo(x IN VARCHAR2) RETURN VARCHAR2
A procedure called bar that takes no arguments would be specified like this:
PROCEDURE bar
Code stored in the code table can have their specifications stored in their source. To do this, place a line containing __END__ at the end of your code, and type the specification anywhere after this. Here's an example:
sub subtract
{
	return $_[0] - $_[1];
}

__END__

FUNCTION subtract(x IN PLS_INTEGER, y IN PLS_INTEGER) RETURN PLS_INTEGER
Specifications for code stored in the bootstrap file must be passed to the create_wrapper or import_perl procedures (see below).

NOTE: __END__ is a special tag that tells Perl not to parse anything beyond that point.

Import Your Code
You can skip this section if your code is in the bootstrap file.

Use the import_perl procedure to import your code into the database. If a specification was included in the source file, the wrappers will be create for you at this point, and you can skip the next step.

Create Wrapper Code
You can skip this section if you declared the specification in the source file.

You now need to create an external procedure specification that will glue together Oracle and your Perl code. extproc_perl can do this for you by running the Perl.create_extproc procedure. Pass it the specification for your procedure, and it will generate a file in the trusted code directory containing the DDL for your procedure. Here's an example using our subtract function, which accepts 2 integer arguments and returns an integer:

SQL> exec Perl.create_extproc('subtract(a IN PLS_INTEGER, b IN PLS_INTEGER) RETURN PLS_INTEGER');

PL/SQL procedure successfully completed.
NOTE: Perl.import_perl can also create a wrapper if you provide the specification as a third argument to the procedure.
Create the external procedure
The DDL created in the previous step is located in the trusted code directory, named after your procedure with a .sql suffix. In the case of our subtract function, our file is named subtract.sql. All you need to do is run that script. Run sqlplus from the trusted code directory and type @subtract (substitute subtract with the name of your procedure):
SQL> @subtract

Function created.
Versions of extproc_perl prior to 2.50 required relinking after this step. This is no longer necessary.

Returning Values From Procedures

Stored procedures can return values into their arguments if the arguments are declared with a mode of IN OUT (used for input & output) or OUT (used only for output). Making this work in extproc_perl is easy: That's all you need to do. Now, instead of passing the value of arguments to your Perl procedure, extproc_perl will pass a reference to the argument. You will need to dereference it (to a scalar) in order to access the value of the argument.

As an example, let's create a function called double that takes an integer argument and doubles it.

First, let's write the Perl subroutine with the procedure specification at the end:

sub double
{
        my $n = shift;
        ${$n} *= 2; # double the dereferenced scalar
}

__END__

PROCEDURE double(x IN OUT PLS_INTEGER)
Now we can import the procedure, which will also create the wrappers for us:
SQL> exec Perl.import_perl('double','double.pl')

PL/SQL procedure successfully completed.
The next step is to create the external procedure. We'll use the DDL generated for us by Perl.import_perl:
SQL> @double

Procedure created.

Now we can call our procedure, with a little PL/SQL syntactic sugar to help us with the demonstration:

SQL> set serveroutput on
SQL> declare
  2    n number := 5;
  3  begin
  4    double(n);
  5    DBMS_OUTPUT.put_line('The result is '||n);
  6  end;
  7  /
The result is 10

PL/SQL procedure successfully completed.

Using Perl Procedures In Packages

In a production environment, stored procedures are usually declared in PL/SQL packages instead of out in the open in the current schema. You can declare Perl procedures in packages as well, with a few tweaks. First, you need to tell extproc_perl to generate DDL for a package procedure instead of a CREATE statement. There are two ways to do this: Now when you import your code, package DDL will be generated and placed in the trusted code directory just as it was before. But this time, you need to place the contents of the generated file into your CREATE PACKAGE and CREATE PACKAGE BODY statements. This guide assumes you are familiar enough with packages to know how to do this.

From here, you should recompile the package and relink extproc_perl.

NOTE: You may need to explicitly grant SELECT privileges to the invoking user on the code table. Roles are ignored when running procedures from Oracle packages (including public).

Datatypes

As of version 2.00, extproc_perl supports the following Oracle datatypes as parameters and return values in directly executed procedures: The following datatypes have support planned for a later release:

Manipulating Oracle Dates From Perl

Oracle dates are passed to Perl as an object of the type ExtProc::DataType::OCIDate. This class has several methods you can use to manipulate the date and time stored inside the object:

new()

Returns a new NULL OCIDate object. Use only when returning a new DATE value from a function.

getdate()

Returns a list containing the year, month, and day of the date stored in the object.

setdate($year, $month, $day)

Sets the date in the object to the year, month, and day you specify.

setdate_sysdate()

Sets the date to the current system date and time.

gettime()

Returns a list containing the hour, minute, and second of the time stored in the object.

settime($hour, $minute, $second)

Sets the time in the object to the hour, minute, and second you specify.

to_char($format)

Returns a string representation of the date and/or time in the format you specify. The format should be a standard Oracle date format (see Oracle's documentation for details). This function mimics PL/SQL's to_char function.

Database Callbacks

One of the most powerful features of extproc_perl is its ability to access the calling database without incurring the overhead of a new SQL*Net connection. In fact, no user names, passwords, or service names are required at all. extproc_perl hooks into DBI via a patch to DBD::Oracle (integrated in version 1.15), letting you use standard DBI methods to access the database.

The only difference between using DBI in a stored procedure and using DBI from a regular Perl script is the way you initialize the connection. Actually, "connection" is a misnomer here, as the communication between Oracle and the DBI in a stored procedure is stateless, but we'll call it a connection for the sake of clarity.

Instead of using DBI->connect to connect to the database, you create a new ExtProc object and call its dbi_connect method. You don't need to pass any arguments to it; it will automatically create the infrastructure necessary to communicate with the calling database, including the essential DBI internals. It returns a standard DBI handle that you can use with the familiar DBI methods to query or update the database. These queries are called callbacks. Here's a quick example:

use ExtProc;

sub get_address
{
	my $name = shift;
	my $e = ExtProc->new;
	my $dbh = $e->dbi_connect;
	my $sth = $dbh->prepare("select address from accounts where name = ?");
	$sth->execute($name);
	my $address = ($sth->fetchrow_array)[0];
	$sth->finish;
	return $address;
	$dbh->disconnect;
}
Unlike mod_perl, you do not establish a persistent database connection at the start that you reuse for subsequent procedure calls. This is because, as was mentioned before, there is no real connection. Every time an external procedure wants to access the database via callbacks, it must first initialize its "context", which provides it with the necessary means for communicating with the database. This initialization must be repeated at the beginning of each call to an external procedure, but fortunately it is an extremely lightweight process and is barely noticeable performance-wise, if at all. It doesn't really matter anyway, as callbacks from extproc_perl are much faster than queries over even an already established SQL*Net connection.

There are several limitations to callbacks, the most important of which are:

Programming Guidelines

Programming in extproc_perl is not much different than programming regular Perl scripts, but there are several important things to keep in mind that will help you avoid problems.

Global Variables

Each extproc_perl session is executed in its own namespace. Any global variables from one Perl procedure will be accessible from other Perl procedures in the same session. It is therefore crucial to avoid name clashes between global variables in different procedures. This is easy if there is just one developer, but it can become unmanageable as more people write Perl procedures for the same database. There is a fix on the way to execute each procedure in its own namespace, a la mod_perl, but for now, unless you need data persistence across procedure calls, avoid global variables. NOTE: You can create unique Perl packages within your code to store per-procedure global variables if you really need them and want to guard against name clashes.

Secure Programming

All production databases should have tainting enabled in the extproc_perl configuration file. Tainting marks each argument to a procedure as tainted, meaning that it should not be used in "dangerous" operations until its contents have been verified and untainted. Dangerous operations include those that access the filesystem and run shell commands, just to name a few. Untainting usually involves extracting data from a pattern match to verify that the data is in an appropriate format for your procedure to use. See the perlsec(1) POD documentation for more information on tainting.

Handling NULL Values

For simple Oracle types, NULL values are passed to Perl as an undefined scalar. Likewise, returning undef from a Perl subroutine or via a IN OUT or OUT parameter will pass a NULL value back to Oracle.

Other Oracle types, like DATE, are not represented easily by a single scalar value, so extproc_perl provides a class for implementing each type as an object. The "nullity" of these objects can be manipulated via two standard methods: is_null and set_null. is_null will return 1 if the object is NULL, and 0 otherwise. set_null will mark an object as NULL, regardless of its current value. Setting the value of a NULL object will automatically remove its NULL status.

Dying Gracefully

Never use exit to terminate your Perl code. It will kill the external process and sever the communication between extproc_perl and the database. If you need to exit gracefully, use die; it will raise an Oracle exception and report the failure back to the database.

NOTE: exit() may be wrapped in a future release of extproc_perl to prevent "bad things" from happening from its casual use. A tip of the hat to mod_perl.

extproc_perl Views

It was very difficult in extproc_perl version 1 to ascertain certain aspects of the configuration and status of the running extproc_perl engine. Simple questions like, "What is the path to my bootstrap file?" couldn't be answered without going back to the original build directory. extproc_perl 2.0 solves this problem in a classic Oracle fashion, by providing special views that you can query from the database: perl_config and perl_status.

The perl_config View

perl_config is a static view containing the settings from your configuration file. Results from queries on this view are usually consistent throughout the life of any particular session. It is defined as follows:
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 BOOTSTRAP_FILE                                     VARCHAR2(4000)
 CODE_TABLE                                         VARCHAR2(4000)
 INC_PATH                                           VARCHAR2(4000)
 DEBUG_DIRECTORY                                    VARCHAR2(4000)
 MAX_CODE_SIZE                                      VARCHAR2(4000)
 MAX_SUB_ARGS                                       VARCHAR2(4000)
 TRUSTED_CODE_DIRECTORY                             VARCHAR2(4000)
 TAINTING                                           VARCHAR2(4000)
 DDL_FORMAT                                         VARCHAR2(4000)
 SESSION_NAMESPACE                                  VARCHAR2(4000)
 PACKAGE_SUBS                                       VARCHAR2(4000)

The perl_status View

The perl_status view warrants a bit more explanation than the previous view. It is a dynamic view, in that it reports on the state of the extproc_perl engine at the time of the query. It is defined as follows:
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EXTPROC_PERL_VERSION                               VARCHAR2(4000)
 DEBUG_STATUS                                       VARCHAR2(4000)
 DEBUG_FILE                                         VARCHAR2(4000)
 PACKAGE                                            VARCHAR2(4000)
 ERRNO                                              VARCHAR2(4000)
 ERRSV                                              VARCHAR2(4000)
And here's what it all means:
ColumnDescription
EXTPROC_PERL_VERSIONVersion of extproc_perl and the Perl interpreter it was linked against
DEBUG_STATUSIs debug mode enabled or disabled?
DEBUG_FILEThe current or most recently used debug file
PACKAGEThe current session's package name, if any
ERRNOThe most recent system error message ($! in Perl)
ERRSVThe most recent eval error message ($@ in Perl)

More columns will likely be added in the future as more internal aspects of the extproc_perl engine are exposed.

Utilities

PL/SQL Utilities

The ExtProc::Util module contains some handy Perl procedures for use in queries and PL/SQL stored procedures. To use them, include use ExtProc::Util qw(:all); in your bootstrap file.

match(string, regex)

The match function will return 1 if string matches the supplied Perl regular expression regex, and 0 otherwise.

substitute(string1, regex, string2)

The substitute function will replace all occurrences of the pattern regex in string1 with the literal string2 and return the result. It does not actually modify string1.

Perl.flush

This procedure destroys the current interpreter and its data, but leaves the loaded configuration intact.

Perl Utilities

extproc_perl also provides some useful tools for your scripts. These can be imported from the ExtProc module.

put_line(string)

Calls DBMS_OUTPUT.put_line to send raw output back to the caller. You must have enabled the display of this output, which in sqlplus can be done by typing set serveroutput on at the SQL> prompt.

Alternatively, you can use the filehandle interface to put_line, which let's you use Perl's native output functions like print and write. You can retrieve the proper filehandle by creating a new ExtProc object and calling the fh method:

use ExtProc;

my $e = ExtProc->new;
my $fh = $e->fh;

print $fh "Hello, sqlplus!";
NOTE: put_line and its filehandle interface will add a newline to your string; there's no need to do it yourself. NOTE: Output is buffered until your code returns; you won't see any output until control is passed back to the database.

is_function()

Returns true if the current subroutine has been called as a function.

is_procedure()

Returns true if the current subroutine has been called as a procedure.

ora_exception(message)

Raises a user-defined Oracle exception (ORA-20100) containing the message you passed as an argument. You should immediately return from your subroutine after calling ora_exception. NOTE: die will also raise an Oracle exception, with the added benefit of immediately returning control to the database.

register_destructor(coderef)

Register a destructor with extproc_perl to be called before the session exits. coderef can be a reference to a subroutine or an anonymous subroutine. register_destructor is a method, so you need to create an ExtProc object first.
use ExtProc;

sub my_exit
{
	some_cleanup;
}

my $e = ExtProc->new;
$e->register_destructor(\&my_exit);
Destructors are called in LIFO (last in, first out) order. They cannot access the database via callbacks and must be declared before they are called. This means you should store destructor subroutines in the source files where they are registered, or in the bootstrap file.

Debugging

The debugging facilities in extproc_perl include a simple trace mechanism and standard error redirection. You can also hook into the debugging facility through Perl so you can write to the debug log from your code.

Debugging Overview

To enable debugging, pass a 1 to the Perl.debug procedure:
SQL> exec Perl.debug(1)

PL/SQL procedure successfully completed.
All access to extproc_perl and standard error output from here on will be logged by the debugger until you disable debugging. To do that, just pass a 0 to Perl.debug.

The debugger allocates one debug file per session. You can find the path to the debug file in the DEBUG_FILE column of the perl_status view. It will contain either the active debug file or the most recently used debug file in case debugging has been disabled. As an example, here's the debug output for a call to our double procedure that we created earlier in this document:

SQL> set serveroutput on
SQL> exec Perl.debug(1)

PL/SQL procedure successfully completed.

SQL> declare
  2>   n NUMBER(10) := 12;
  3> begin
  4>   double(n);
  5>   DBMS_OUTPUT.put_line("Result is "||n);
  6> end;
  7> /
Result is 24

PL/SQL procedure successfully completed.

SQL> exec Perl.debug(0)

PL/SQL procedure successfully completed.

SQL> select debug_file from perl_status;

DEBUG_FILE
--------------------------------------------------------------------------------
/tmp/ep_debug.2636

SQL> !cat /tmp/ep_debug.2636
Fri Dec 26 15:54:11 2003 START
Fri Dec 26 15:54:17 2003 IN (user defined) EP_double(0x806f5b4, ...)
Fri Dec 26 15:54:17 2003 -- prototype: double(n in out PLS_INTEGER)
Fri Dec 26 15:54:17 2003 IN pl_startup(0x40bb58e0)
Fri Dec 26 15:54:17 2003 -- Perl interpreter created: p=0x80f2e18
Fri Dec 26 15:54:17 2003 IN inc_path_to_mflag(0x40bb58e0)
Fri Dec 26 15:54:17 2003 -- using mflag '(null)'
Fri Dec 26 15:54:17 2003 RETURN pl_startup
Fri Dec 26 15:54:17 2003 -- perl_parse argv: '-T','-e','0'
Fri Dec 26 15:54:17 2003 -- using bootstrap file '/var/tmp/boot.pl'
Fri Dec 26 15:54:17 2003 -- using namespace ExtProc::Session2984
Fri Dec 26 15:54:17 2003 -- bootstrapping successful!
Fri Dec 26 15:54:17 2003 RETURN EP_double
Fri Dec 26 15:54:17 2003 IN parse_code(0x40bb58e0, 0xbfffeb50, 'double')
Fri Dec 26 15:54:17 2003 -- fully qualified sub name is 'ExtProc::Session2984::double'
Fri Dec 26 15:54:17 2003 -- attempting to fetch code from database
Fri Dec 26 15:54:17 2003 IN fetch_code(0x40bb58e0, 0xbfffeb50, "double")
Fri Dec 26 15:54:17 2003 RETURN parse_code
Fri Dec 26 15:54:17 2003 -- eval'ing fetched code
Fri Dec 26 15:54:17 2003 -- CV is cached
Fri Dec 26 15:54:17 2003 RETURN (user defined) EP_double
Fri Dec 26 15:54:17 2003 -- about to call call_pv()
Fri Dec 26 15:54:17 2003 -- call_pv() returned 1
Fri Dec 26 15:54:26 2003 IN ora_perl_debug(0x806f5b4, 0)
Fri Dec 26 15:54:26 2003 STOP
NOTE: To see if debugging is enabled, look at the DEBUG_STATUS column of the perl_status view.

Debugging From Perl

You can use the ep_debug subroutine from the ExtProc module to write to the debug log. If debugging is disabled, ep_debug is a no-op.
use ExtProc qw(ep_debug);

sub foo
{
	ep_debug("I'm in 'foo' right now");
}
You can also write to the debug log via STDERR, but this is written in raw format, without timestamps. You should generally leave STDERR alone, letting it capture diagnostic output from Perl that you otherwise wouldn't see from Oracle, like messages from warn or use strict.

$Id: userguide.html,v 1.31 2006/04/20 15:43:05 jeff Exp $