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.
listener.ora or ask your DBA).
dba)
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
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:
ORACLE_SID environment variable to the name of your
database.
ORACLE_USERID environment variable with your username
and password separated by a slash (e.g. user1/foobar)
SID_DESC section of your external procedure listener
configuration:
(ENVS="EXTPROC_DLLS=ANY")
(SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/9.2.0) (PROGRAM = extproc32) (ENVS="EXTPROC_DLLS=ANY") )
lsnrctl reload and you should be all set.
NOTE: Remove this configuration once you're finished testing. You'll want to replace ANY with the path to the installed extproc_perl.so.
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.
SQL> @ep_schema Table created. View created. View created. No errors.
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.
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.
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
/tmpnoDigest::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.
yesyesuse 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.
noyes 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.
standardstandard format will write a PL/SQL
CREATE statement. package format will write the DDL
for inclusion in a PL/SQL package.
3276832extproc_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.
NOTE: The bootstrap file should be placed on a local file system to ensure that it is always available to the local database.
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.
import_perl if the file has the same name as the procedure it
contains, with a .pl extension.
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 wrapper
Perl.proc wrapper
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 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:
IN parameters are supported, so you cannot return data
from a procedure or return data other than the return value from a function
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
In general, there are three steps when creating a directly executable procedure:
FUNCTION|PROCEDURE name [(arg1 IN|IN OUT|OUT datatype, [...],)] [RETURN datatype]
foo that takes one VARCHAR2 argument and returns a VARCHAR2 would be specified as follows:
FUNCTION foo(x IN VARCHAR2) RETURN VARCHAR2
bar that takes no arguments would be specified
like this:
PROCEDURE bar
__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
create_wrapper or import_perl procedures (see below).
NOTE: __END__ is a special tag that
tells Perl not to parse anything beyond that point.
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.
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.
Perl.import_perl can also
create a wrapper if you provide the specification as a third argument to
the procedure.
.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.
IN OUT (used for input & output) or
OUT (used only for output). Making this work in extproc_perl is
easy:
IN OUT or OUT when
creating the wrapper.
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)
SQL> exec Perl.import_perl('double','double.pl')
PL/SQL procedure successfully completed.
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.
CREATE statement. There are two ways to do this:
ddl_format from
standard to package. This value will be used as the
default setting for all subsequent sessions.
Perl.ddl_format('package') to use the
package format for this session only. The default setting in
extproc_perl.conf will take effect for the next session.
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).
ExtProc::DataType::OCIDate. This class has several methods
you can use to manipulate the date and time stored inside the object:
to_char
function.
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;
}
There are several limitations to callbacks, the most important of which are:
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.
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.
perl_config and
perl_status.
perl_config Viewperl_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)
perl_status Viewperl_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)
| Column | Description |
|---|---|
| EXTPROC_PERL_VERSION | Version of extproc_perl and the Perl interpreter it was linked against |
| DEBUG_STATUS | Is debug mode enabled or disabled? |
| DEBUG_FILE | The current or most recently used debug file |
| PACKAGE | The current session's package name, if any |
| ERRNO | The most recent system error message ($! in
Perl) |
| ERRSV | The 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.
use ExtProc::Util qw(:all); in your bootstrap file.
match function will return 1 if string matches the
supplied Perl regular expression regex, and 0 otherwise.
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.
ExtProc module.
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!";
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.
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.
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);
Perl.debug procedure:
SQL> exec Perl.debug(1) PL/SQL procedure successfully completed.
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
DEBUG_STATUS column of the perl_status view.
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");
}
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.