Section 8.1. Communication Between Perl and PLSQL

8.1 Communication Between Perl and PL/SQL

The way you combine Perl and PL/SQL, and get the most out of these two excellent languages, is with Perl's extproc_perl module, created by Jeff Horwitz. PL/SQL is able to communicate with this library via its external procedure C-library system, which is known as EXTPROC .

8.1.1 What are External Procedures?

External procedure is a generic term for a server-side program that you can compile into the native "shared library" format of the operating system. Under Unix, a shared library is a shared object or .so file. Under Win32, it's a DLL (dynamically linked library). You can write an external procedure in any language you wish, as long as your compiler, interpreter, and/or linker will generate the appropriate shared library format that is callable from the language you have used. Historically, most external procedures called from PL/SQL have been C programs, but Perl is now becoming a popular option. This is because it is now possible to embed Perl directly within a C library via Doug MacEachern's ExtUtils::Embed module, as shown in Figure 8-1.

Figure 8-1. Embedding Perl directly within a C library

8.1.2 Embedding Perl in C

The ExtUtils::Embed module was actually developed as a way of embedding the Perl interpreter within C, via Apache, specifically for mod_perl . Our ability to use it with PL/SQL is really a byproduct of that development. To find out more about its use, run the following command:

 $ perldoc perlembed

Instead of having to work out all the information necessary to embed Perl into C, all we have to run now is a command such as the following:

 $ cc - o myCexecutable myCexecutable.c \    `perl -MExtUtils::Embed -e ccopts -e ldopts`

This determines everything your C program will need in order to embed Perl, including the three main C- related files held under the Perl library tree: EXTERN.h , perl.h, and libperl.a . It's a particularly good technique, because the embedded Perl interpreter is still able to read and interpret independent Perl scripts, a fact we rely on with our embedded Perl system, as we'll explain later in this chapter. Although the two header include files are important, it's the libperl.a archive which is the really crucial file to include; this archive contains the core of Perl's interpreter C code.

8.1.3 Calling the Embedded Perl C Library from PL/SQL

Once we've compiled our new library with its embedded Perl system (we'll show how to do that shortly), we can start using it like any other efficient C-based EXTPROC library. (We create our new one with a special ExtProc.pm Perl module; this sits on top of the standard Perl interpreter, plus any other modules, such as Perl DBI, that we require.)

Let's look at what happens when you call this embedded Perl C library from PL/SQL in order to call a particular Perl function; numbers are keyed to the step numbers shown in Figure 8-2.

The process starts with a PL/SQL client application calling a special PL/SQL "module body."
PL/SQL looks for a special Net8 listener process ^[2] that should already be running in the background.

^[2] Net8 is known as Oracle Net in Oracle8 i and later releases and SQL*Net in Oracle7 and earlier releases.
At this point, the listener spawns an Oracle executable program called EXTPROC (note the uppercase name on this program).
EXTPROC loads the dynamic library ( extproc_perl.so ), and then invokes this library with the specified Perl function call.
The library then interrogates a Perl boot script (in this case, ora_perl_boot.pl ) and looks for the requested function.
On finding it, it executes the Perl function code, deals with any parameters, and then returns the results all the way back to the calling PL/SQL client.
The extproc_perl.so C library returns these results via the EXTPROC process.
The EXTPROC process channels these results into the PL/SQL Runtime Engine.
And finally, the PL/SQL Runtime Engine returns these results to the calling PL/SQL application code.

Figure 8-2. extproc_perl in action