Section 8.2. Embedding Perl Within Oracle

8.2 Embedding Perl Within Oracle

Running extproc_perl is mainly about getting the Oracle external procedures system working correctly. One of the best general guides we've found for these installation procedures is Chapter 23 of Oracle PL/SQL Programming , 3 ^rd ed. (now covering Oracle9 i ) by Steven Feuerstein with Bill Pribyl (O'Reilly & Associates, 2002). For further information on Oracle's external procedures, the best online information source is perhaps http://technet.oracle.com. Although the pages are very fluid (you may need to browse around a bit), we found the following pages useful when we were investigating the subject:

http://download.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a88876/adg11rtn.htm: Oracle9 i Application Developer's Guide ” Fundamentals for Release 1, Chapter 10.
http://download.oracle.com/otndoc/oracle9i/901_doc/server.901/a90117/manproc.htm: Oracle9 i Database Administrator's Guide for Release 1, Chapter 5.

8.2.1 EXTPROC security

Before you get too deeply into the details of extproc_perl , we recommend that you check out the following page for possible security alerts about EXTPROC , the actual program spawned by the Oracle external procedure listener processes:

http://otn.oracle.com/deploy/security/alerts.htm

Because of the nature of what EXTPROC does ” using external libraries to access the inside of the Oracle database ” we have to be vigilant in our use of the EXTPROC system provided by Oracle. We recommend that you carefully follow the guidelines provided in any relevant security advisories you find on the web page we've referenced previously.

8.2.1.1 extproc_perl and Win32

Like Oracle::OCI , which we described in the previous chapter, extproc_perl ^[3] is still in something of an experimental stage. At the time this book went to press, there still wasn't a PPM available for installation on Win32 systems. Once the module is fully mature, we're sure it will become available on a PPM repository. Keep checking at the following page for more details or at the download sites mentioned in subsequent sections:

^[3] Also known as the "Oracle Perl Procedure Library."

http://dbi.perl.org

(Compilation on Win32, with commercial compilers, should follow the same logical steps as the Unix installation process described shortly ” your mileage may vary.)

8.2.2 A Detailed Look at extproc_perl

Figure 8-3 and Figure 8-4, in combination, show how we can track the dynamic calling of an external Perl procedure from within a PL/SQL code block. (We've also included some Oracle library configuration information in Figure 8-3 that is presented in greater detail later in this chapter.)

Figure 8-3. PL/SQL's active linkage to extproc_perl #1

Figure 8-4. PL/SQL's active linkage to extproc_perl #2

Let's see what's going on here:

From deep within the database, the PL/SQL program broadcasts to the EXTPROC listener. It sends out targeting information, stored within library and function declarations, so the listener can locate the correct code within the external procedure. It also sends any required parameters.
The listener picks up the signal from the PL/SQL engine.
It then launches the EXTPROC rocket program (or spawns it, as the manuals say, which is too Borg for those of us who are followers of the One True Kirk.)
Once EXTPROC is deployed, it takes over mission control, and coordinates the entire operation between the PL/SQL ground station and the external C program agents . It maps shared code pages into the address space of the user process and maintains this link until the client session completes. It then retracts its panels and splashes back down, to be sent up again on later missions. While on station, EXTPROC deals with all requests by the client session for external procedural help.

8.2.3 Downloading extproc_perl

You can download the latest stable version of extproc_perl from here:

http://www.smashing.org

http://www.cpan.org/modules/by-authors/Jeff_Horwitz

8.2.4 Setting Up External Procedures

Setting up external procedures is not simple. You will need to do quite a bit of work to get the setup right. We've summarized the main steps here; in the following sections we'll show the details for each point:

Add a tnsnames.ora entry for the EXTPROC listener process, which calls the EXTPROC program. This should be installed in $ORACLE_HOME/bin .
Edit the listener.ora file by adding an entry for the "external procedure listener."
Start a separate listener process to exclusively handle external procedures.

The EXTPROC process, launched by the listener, inherits the operating system privileges of the listener. Therefore, Oracle recommends that privileges for a separate listener process be made restrictive . They should lack the ability to read or write to database files or to the server address space. To provide this level of security, you may want to run your listener as an OS user with limited permissions, such as nobody .

Now let's look at the setup details:

With every significant release of Oracle, the configuration of the .ora files in $ORACLE_HOME/network/admin seems to change. We recommend that you refer to your own installation configuration details for the exact setup required by your system. We'll concentrate on the logical semantics here, rather than the exact details for each version. A typical tnsnames.ora , on the same server as the listener, should be given a new entry such as the following. (This is different for Oracle9 i ; see the discussion later for details):
```
  extproc_connection_data  =  (DESCRIPTION =    (ADDRESS = (PROTOCOL=IPC)(KEY=  extproc_key  ))    (CONNECT_DATA = (SID =  extproc_agent  ) ) 
```
In some examples, the basic entry name , extproc_connection_data , is fixed. However, even if this is the case in your version of Oracle, it may need a suffix if your sqlnet.ora contains a default domain name such as:
```
 NAMES.DEFAULT_DOMAIN=ORACLE.OREILLY.COM 
```
You may need to change the server tnsnames.ora entry name to match the domain name entries as follows :
```
 extproc_connection_data.ORACLE.OREILLY.COM = ... 
```
However, the key you specify (in this case extproc_key ) must also match the KEY you specify in the listener.ora file. In addition, the SID name you specify (in this case extproc_agent ) must match the SID entry in the listener.ora file. (You may just want to call everything extproc to keep it simple.) In the following, we've attached entries to a new listener entry in order to run up a separate listener purely for external procedures:
```
 EXTERNAL_PROCEDURE_LISTENER =  (ADDRESS_LIST =     (ADDRESS = (PROTOCOL=ipc)                (KEY=  extproc_key  )      ) )    SID_LIST_EXTERNAL_PROCEDURE_LISTENER = (SID_LIST =      (SID_DESC = (SID_NAME=  extproc_agent  )                  (ORACLE_HOME=/u02/app/oracle/product/8.0.4)                  (PROGRAM=  extproc  )       ) ) 
```
Note the following conditions for the preceding listener.ora example:
- The EXTPROC program is conventionally referred to as extproc in lower case.
- The ORACLE_HOME must be set to the Oracle software home.
- The EXTPROC executable must exist in $ORACLE_HOME/bin .
- However, in Oracle9 i , most things are automatic with PLSExtProc . With a small change to DBD::Oracle , described later, this is fine. Let's examine two snippets from the two main Oracle9 i .ora files. First, listener.ora :
```
 (SID_DESC =   (SID_NAME =  PLSExtProc  )   (ORACLE_HOME = /opt/oracle/product/9.0.1)   (PROGRAM =  extproc  ) ) 
```
  And now, tnsnames.ora :
```
 EXTPROC_CONNECTION_DATA.LOCAL =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = IPC)(KEY =  EXTPROC  ))     )     (CONNECT_DATA =       (SID =  PLSExtProc  )       (PRESENTATION = RO)     )   ) 
```
Once ready, we can start up a separate listener as a low-privilege user:
```
 $ lsnrctl start EXTERNAL_PROCEDURE_LISTENER 
```

Once the listener is running successfully, we can skip the following section. However, it may prove useful if you encounter any listener problems.

8.2.5 Debugging External Procedure Listeners

You'll be among friends if your listener setups refuse to work first time around. This badge of honor even has a special debug routine to help you; look for the following file under ORACLE_HOME :

dbgextp.sql

Before you install this file, be sure to read it; it contains some good documentation regarding how you can make use of it with debugging programs.

Now follow these steps:

In a perfect world, you should get no errors when you execute the STARTUP_EXTPROC_AGENT procedure. Notice that in addition to CONNECT and RESOURCE, other important privileges granted to the new user include both CREATE ANY LIBRARY and DROP ANY LIBRARY:

 SQL> connect system/manager SQL> create user extproctest identified by extproctest; SQL> grant connect, resource to extproctest; SQL> grant create any library, drop any library to extproctest; SQL> connect extproctest / extproctest SQL> @?/plsql/demo/dbgextp.sql  SQL> call DEBUG_EXTPROC.STARTUP_EXTPROC_AGENT(  );   Call completed  .

If STARTUP_EXTPROC_AGENT refuses to fire, this will indicate that the .ora files have a configuration problem of some kind. Once everything's ship shape, drop this test user:
```
 SQL> connect system/manager; SQL> drop user extproctest cascade; 
```

The DEBUG_EXTPROC package can be made to work with popular C program debug utilities. If you don't have a debugger on your system and you're using gcc , you will be able to use the excellent gdb debugger, which is designed to work hand-in-glove with gcc . See http://www.gnu.org/software/gdb/.

8.2.6 Building a New Perl

Before doing anything else, you will need to establish whether you're using a Perl distribution with a shared libperl . This is a pre-condition for extproc_perl . In the following sections we'll see how to find this out and how to build a new Perl if you need to do so.

8.2.6.1 The need for a shared libperl

To find out if you are using a Perl distribution with a shared libperl , you can issue the following:

 $ perl -MConfig -e 'print "$Config{useshrplib}\n"'  false

Alas, false was the wrong answer. But, every cloud has a mithril silver lining . Because we're pointing Perl directly into the heart of the Oracle database, we'll do as Jeff Horwitz actually recommends and build a special version of Perl, just for Oracle's use. This way, we can do all the things we need to do without clobbering anyone or anything else along the way.

We're also going to make use of a DBD::Oracle patch, supplied within the extproc_perl download, to rebuild Perl DBI. This makes it doubly sensible to break out a fresh Perl to play with.

Because Oracle lacks support for the dynamic loading of shared objects from external procedures, Perl's DynaLoader is compromised. We have to load shared objects from targeted modules at runtime, and this static architecture requires XS hooks, special pleading, and a delegation of Papal Nuncios from Rome. Building a brand new Perl is definitely the way to go!

8.2.6.2 Building Perl for the oracle user

In this section, we'll work through how to build a brand new Perl for the oracle user. You may want to breeze through Chapter 2, again to remind yourself about the basics of Perl installation, but we'll do an abbreviated installation run right here and now, and assume that the oracle user's HOME directory is:

 /opt/oracle

If security is an issue, you may wish to create this new Perl for whichever user you run your listeners with (see our earlier note on listener security):

Once the Perl installation user is chosen , you may want to create a new directory in the $HOME directory (to store the forthcoming downloads) and a related perl/bin directory (where we'll ultimately install Perl):
```
 $ cd $HOME $ mkdir perldown $ mkdir -p perl/bin 
```

Next , get and unpack stable.tar.gz from http://www.perl.com/CPAN/src:

 $ cd ../perldown $ gzip -d stable.tar.gz $ tar xvf stable.tar $ cd perl-5.6.1

Configure in a shared libperl and a Perl home of /opt/oracle/perl :
```
 $ rm -f config.sh Policy.sh $ sh Configure  -Dprefix=/opt/oracle/perl -Duseshrplib  
```
The configurator will ask lots of questions, depending on your setup. We have to be careful here and resist pumping the RETURN key like a Mot rhead drummer . You must say "no" to the following question:
```
 Many scripts expect perl to be installed as /usr/bin/perl. I can install the perl you are about to compile also as /usr/bin/perl (in addition to  /opt/oracle  /perl/bin/perl). Do you want to install perl as /usr/bin/perl? [y]  n  
```
This is an exception; aside from the use of a shared libperl , we do intend to build a totally regular, though local, Perl.
Once Perl is configured, run make :
```
 $ make 
```
You may find time to make a nice hot cup of tea, while the Perl monkey spends a couple of minutes churning the compilation organ. When it's completed, check it over:
```
 $ make test ... All tests successful. u=0.93  s=0.12  cu=64.77  cs=8.81  scripts=249  tests=12503 
```
Now let's go create (note that for once we can avoid doing this as root ):
```
 $ make install 
```
At some point during this installation, you may get the following warning:
```
 Warning: perl appears in your path in the following locations  beyond where we just installed it:    /usr/bin/perl 
```
This is kind of like a private sentry turning back a known Four Star General because of a forgotten password; it's a good thing. To get around this, we must make sure our local hero Perl comes ahead of any others when we compile our library. Once the compilation has finished, you'll still see the older Perl before our nice new sparkling one:
```
 $ type perl perl is hashed (/usr/bin/perl) 
```
We can get round this immediately by resetting PATH :
```
 $ export PATH=$HOME/perl/bin:$PATH $ type perl perl is  /opt/oracle/perl/bin/perl  
```
(We'll also have to do this more permanently via whatever profiling system we're using, to ensure that our oracle user always gets the right Perl.)
Now, the proof of the Christmas pudding is in the eating , so let's see if we've acquired the use of a shared libperl . Go to it, Red:
```
 $ perl -MConfig -e 'print "$Config{useshrplib}\n"'  true  
```

Next, we can move on and install Perl DBI and DBD::Oracle over the fresh new Perl.

8.2.7 Perl DBI and DBD::Oracle

As Figure 8-5 shows, once PL/SQL calls the embedded Perl interpreter via external procedures, it's pretty much out on a limb in the outside world. Therefore, although we can return ordinary values to the host database, we need to use DBI (using its own form of SQL) if we wish to loop back. This loopback behavior is displayed in Figure 8-6. (Notice that we can connect to other databases as well, although we have to establish a proper connection in these cases.)

Figure 8-5. The basic circuitry of extproc_perl

Figure 8-6. Using Perl DBI in loopback mode

8.2.7.1 The importance of OCIExtProcContext

The OCIExtProcContext structure from OCI, originally set up when extproc_perl is first called, gives DBI the ability to remain within the current PL/SQL transaction. This prevents the need for a new database connection to be set up. (We'll say more about this shortly.)

There is a slight complication to DBI, however, caused by the use of OCIExtProcContext . In order to get DBI to work within our Oracle Perl interpreter, we needed to apply a patch to DBD::Oracle . This patch will be included in versions of DBD::Oracle , from 1.13 onward, but the extproc_per-0.93.tar.gz tarball we downloaded had the patch designed for DBD::Oracle 1.08 , and we were still on DBD::Oracle 1.12. To deal with this incompatibility , we therefore obtained DBD::Oracle 1.08, just to make sure the patch we had access to would work as expected. To complete our tarball set, we obtained the following files, including Digest::MD5 for testing purposes, and copied them to /opt/oracle/perldown :

http://www.cpan.org/authors/id/TIMB/DBI-1.20.tar.gz

http://www.cpan.org/authors/id/TIMB/DBD-Oracle-1.08.tar.gz

http://www.cpan.org/authors/id/JHORWITZ/extproc_perl-0.93.tar.gz

http://www.cpan.org/authors/id/GAAS/Digest-MD5-2.16.tar.gz

Before we went into combat, we extracted the Perl ammunition :

 $ gzip -d *.gz $ tar xvf DBI-1.20.tar $ tar xvf DBD-Oracle-1.08.tar $ tar xvf extproc_perl-0.93.tar $ tar xvf Digest-MD5-2.16.tar

You may want to get rid of all these .tar files when you've finished the installation, but we always tend to keep these until the bitter end, along with our lucky rabbits' feet.

8.2.7.2 Patching DBD::Oracle

At this point, you may find it useful to go into the extproc_perl distribution directory, and check on the documentation:

Open up the README.DBI . This contains the information on the DBI patch:
```
 $ cd extproc_perl $ vi  README.DBI  
```

We also need to make sure we're using the right Perl:

 $ type perl perl is hashed (  /opt/oracle/perl/bin/perl  )

Now we can install DBI, confident that we're dealing with the right Perl agent (those agents can get tricky when you've got more than one of them):
```
 $ cd ../DBI-1.20 $ vi README  $ perl Makefile $ make $ make test $ make install ... Writing  /opt/oracle/perl/lib  /site_perl/5.6.1/i686-linux/auto/DBI/.packlist 
```
Now the patch comes into play. Go to DBD::Oracle 's installation home:
```
 $ cd ../DBD-Oracle-1.08 
```

It's time to start up our target Oracle database and make sure its listeners are fired up. Make sure that ORACLE_HOME , ORACLE_SID , and ORACLE_USERID are all set, as per the DBD::Oracle README file:

 $ export ORACLE_USERID=scott/tiger@orcl.world $ env  grep ORACLE ORACLE_SID=orcl.world ORACLE_USERID=scott/tiger@orcl.world ORACLE_HOME=/opt/oracle/product/9.0.1

The DBD::Oracle patch is worth a look if you have time. You'll notice a sustained use of OCIExtProcContext and other OCI code elements, as from Figure 8-5 and Figure 8-6. We've detailed a snippet or two here:

 ...   #ifdef OCI_V8_SYNTAX +    SV **svp; +    struct  OCIExtProcContext  *this_ctx; ... +      if (sv_isa(*svp, "  ExtProc::OCIExtProcContext  ")) { +         IV tmp = SvIV((SV*)SvRV(*svp)); +         this_ctx = (struct  OCIExtProcContext  *)tmp; +      } ...

To patch DBD::Oracle , carry out the following steps:
```
 $ cp dbdimp.c dbdimp.old $ chmod 644 dbdimp.c 
```

Now move down a directory, as the patch is designed to be applied from the parent directory:

 $ cd .. $ cp extproc_perl/DBD-Oracle.patch . $  patch -p0 < DBD-Oracle.patch   patching file `DBD-Oracle-1.08/dbdimp.c  ' $ cd DBD-Oracle-1.08 $ ls -la dbdimp.*  grep -v '.h' -r--r--r--    1 oracle   oinstall    57336 Apr  7 12:02 dbdimp.c -r--r--r--    1 oracle   oinstall    56354 Apr  7 12:01 dbdimp.old

Note the slightly larger dbdimp.c file.

Oracle9 i users and anyone else with a PLSExtProc listener instance may want to make a very small manual change to the dbdimp.c file before compiling. Look for the following line:
```
 if (!strncmp(dbname,"  extproc  ",  7  )) is_extproc = 1; 
```
Change this to:
```
 if (!strncmp(dbname,"  PLSExtProc  ",  10  )) is_extproc = 1; 
```
This will ensure that the correct database context is called later on.

We can now install DBD::Oracle as usual:

 $ perl Makefile.PL $ make $ make test $ make install

You may also want to install other modules at this point ” for example, Digest::MD5 or anything else from CPAN that catches your fancy. Because we're embedding Perl into a C library, we have to embed everything along with it that we might need later. Fortunately, the main module that makes extproc_perl possible, ExtProc.pm , will always be installed automatically.

8.2.7.3 Connecting back to the host database

As far as the host database goes, when you call the external procedure you remain permanently connected to the database as the PL/SQL client user. However, to use DBI for host callbacks, you can make use of the OCIExtProcContext object, as noted earlier. Fortunately, ExtProc.pm has made this easy. You simply use it to grab the database context from within the bootstrap .pl script file and then use the following code to phone home:

 use DBI; use ExtProc;    # Pick up the current OCI context    my  $context  = ExtProc::context;    # Call back to the host database    my $dbh = DBI->connect( "  dbi:Oracle:extproc  ", "", "",                          { 'context' =>  $context  });

Here's what's going on:

Notice that there is no user or password required with the DBI->connect statement. You're still technically logged into the database as the user who's running the actual PL/SQL and are still part of the current transaction.
Notice as well the database SID, extproc within the DBI driver setup string (you may wish to change this to PLSExtProc , depending on your setup):
```
 dbi:Oracle:  extproc  
```
If you choose a more standard connection, such as dbi:Oracle:orcl , you'll create a proper DBI connection, which incurs significant overhead. You'll also need to supply a user and password. Using dbi:Oracle:extproc is also much faster, as it's tuned directly into OCI.
Alternatively, if you wish to connect to a remote database or to connect as another database user, just connect with DBI normally and follow its standard scott/tiger@my_remote_database_sid pattern. For example:
```
 my $dbh =     DBI->connect( "  dbi:Oracle:my_remote_database_sid  ", "scott", "tiger",                  { RaiseError=>1, AutoCommit=>0 } ); 
```

8.2.8 Installing extproc_perl

We can now move on to the actual installation of extproc_perl . Switch over to the /extproc_perl directory and ritually scan the installation files:

 $ cd ../extproc_perl $ vi README  INSTALL

Note that the INSTALL file is the one you want to be checking here, rather than README .

8.2.8.1 ora_perl_boot.pl

Before configuration, we created a bootstrap Perl script file, ora_perl_boot.pl ; at runtime, the C library will scan this file for functions. ora_perl_boot.pl contains the subroutines we'll be calling later from within PL/SQL. The name of this file on the configuration step will default to:

 $ORACLE_HOME/lib/ora_perl_boot.pl

This seems to be a sensible name. We don't actually have to create this bootstrap file right now (see the note later in this section), but it seems a good time to illustrate doing so. In addition, although test routines are not necessary right now, this also seems as good a time as any to write some in a new $ORACLE_HOME/lib/ora_perl_boot.pl file. (See Example 8-1, and notice our alternative use of PLSExtProc in Test 4, for database context.)

Example 8-1. The ora_perl_boot.pl bootstrap file

 sub localtime { # Test 1 - What's the time Mr Wolf? :-)       my $x = localtime(time);    return $x; };    sub ls { # Test 2.  ==> Hey, this could be rather dangerous! <==       my ($lsarg) = @_;    $lsarg = '.';       my $ls = '/bin/ls -l';    my $lsret = qx( $ls $lsarg );    return $lsret; }    sub md5hex { # Test 3 - A little enigmatic encryption :)       my ($data) = @_;       use Digest::MD5;       my $ctx = Digest::MD5->new;    $ctx->add($data);    my $digest = uc($ctx->hexdigest);    return $digest; }    sub tab_keyword { # Test 4 - Using DBI call-back context       # Pick up the current OCI context and recall host.       use DBI;    use ExtProc;       my($keyword) = @_;       my $context = ExtProc::context;    my $dbh = DBI->connect( "dbi:Oracle:  PLSExtProc  ", "", "",                             {RaiseError=>1, context => $context});        # Viewing all SYSTEM tables, formatted       my $sth = $dbh->prepare( "SELECT table_name " .                               "FROM user_tables ");    $sth->execute;       $sth->bind_columns($table_name);       my $return_string;       my $counter = 0;       while ($sth->fetch) {          $table_name = lc($table_name);       $table_name =~ s/($keyword)/uc()/ieg;  # Hey, Regular                                                # Expressions!!! 8-)       $counter++;          if ($counter > 4) {          $counter = 1;          $return_string .= "\n";       }       $return_string .= sprintf("%-30s ", $table_name);    }     $dbh->disconnect;    return $return_string; }

You can create this boot file after the installation if you wish. As long as the subsequent installation knows where to expect to find it, that's good enough. You can also change the boot file after the installation to add extra subroutines, extra parameters, and so on. The only restriction is that you must use basic Perl, pre-installed modules, or pure Perl modules. If you wish to use a new optional module ” for example, Oracle::OCI ” you must rebuild the extproc_perl.so library with the Oracle::OCI module explicitly mentioned in the build process. Everything you wish to use has to be included within the extproc_perl.so library file, although rebuilding this is fairly painless once you've successfully set up the EXTPROC listener process.

Example 8-1 is not a polished subroutine collection. At this point, we'd recommend that you go back and check the ls( ) subroutine in the example. You could almost drive a Saturn V rocket through its security (or lack of it)! (See Figure 8-7 a bit later for more details.) You will need to watch out for this kind of thing if you employ the rocket thrust power of extproc_perl . For more on Perl security, check out:

 $ perldoc perlsec

8.2.8.2 Installation steps

Follow these steps to install extproc_perl :

Depending on the Oracle version, some header files may be missing from the locations where extproc_perl (originally developed on Solaris) expects to find them. You may have to symbolically link them in where appropriate. For now, though, let's assume that all the files are where we need them:
```
 $ perl Makefile.PL 
```
This step will ask several questions. Because of the restriction on dynamically loaded Perl modules, we have to specifically embed Perl modules statically within our external procedure library via the extproc_perl configuration process. The Makefile.PL configurator will automatically suggest several modules you might like to include. In addition to these, we'll also add the DBI, DBD::Oracle , and Digest::MD5 modules, which we'll be testing later via subroutine md5hex( ) in the boot file:
```
 Modules to include in this    build [IO Socket attrs]: IO Socket attrs  Digest::MD5   DBI DBD::Oracle  <RETURN> 
```

We also accepted the default name and location for the bootstrap file:

 Path to bootstrap     file [  /opt/oracle/product/9.0.1/lib/ora_perl_boot.pl  ]: <RETURN>

It's time to compile:
```
 $ make 
```
We hope you have as much fun as we did with the make step!
We can now create our special library file, which also automatically installs the essential ExtProc.pm Perl module:
```
 $ make install ... *** You should now copy  extproc_perl.so  to a convenient location. ... 
```
So, Mr. Bond, did we cut the library file from the Monte Carlo card pack?
```
 $ ls -la *.so -rwxr-xr-x   1 oracle oinstall 13008 Apr  7 12:18  extproc_perl.so  
```
You win again, Mr. Bond, but we'll be back! Having created a shiny extproc_perl.so library, we place it where Oracle can find it later. $ORACLE_HOME/lib seems the most natural place:
```
 $ cp extproc_perl.so $ORACLE_HOME/lib 
```
Now there's just one more hurdle before the home stretch; we'll discuss it in the next section.

There are two built-in extproc_perl functions detailed in the README.special file. The first is _version , which supplies the current extproc_perl version, and the second is _flush , which destroys the current Perl interpreter and all the Perl data; a new interpreter is started for the next query. (You'll see _flush in action at the end of the chapter in Figure 8-9.)

8.2.9 Deploying extproc_perl

All of the operating system elements are now in place for being able to use extproc_perl . The final task is the creation of the actual PERL_LIB library within the database and its associated perl function. We'll do this in Example 8-2; you can change this code and add more parameters to suit your own environment, either now or at a later time.

Example 8-2. Creating the PERL_LIB library and perl function

 CREATE OR REPLACE LIBRARY PERL_LIB IS    '/opt/oracle/product/9.0.1/lib/extproc_perl.so' /    show error library perl_lib    CREATE OR REPLACE FUNCTION perl (    sub IN VARCHAR2, arg1 in VARCHAR2 default NULL,    arg2 in VARCHAR2 default NULL, arg3 in VARCHAR2 default NULL,    dummy in VARCHAR2 default NULL ) RETURN STRING AS EXTERNAL NAME "ora_perl_sub" LIBRARY "PERL_LIB" WITH CONTEXT PARAMETERS (    CONTEXT,    RETURN INDICATOR BY REFERENCE,    sub string,    arg1 string,    arg1 INDICATOR short,    arg2 string,    arg2 INDICATOR short,    arg3 string,    arg3 INDICATOR short,    dummy string,    dummy INDICATOR short ); / show errors function perl;    create public synonym perl for perl;    grant execute on perl to public;

8.2.10 Testing extproc_perl

To recap, here's what we've done to get ready to run extproc_perl :

The external procedure listener is running and ready to spawn EXTPROC .
The PERL_LIB library has been created, along with the related perl function, and has been made available to public.
The extproc_perl.so library has been compiled and is accessible to EXTPROC .
The ora_perl_boot.pl bootstrap file has been put in place; it is waiting now for calls from the perl function via extproc_perl .

Oh, what a tangled web we've woven ” but one with a huge potential problem. Let's do some testing. You can see our first two tests being called in Figure 8-7; note the security implications of our ls subroutine. Figure 8-8 shows our third test; note how the addition of the md5hex encryption subroutine adds a necessary degree of security.

Figure 8-7. extproc_perl ” tests 1 and 2

Figure 8-8. extproc_perl ” test 3

Figure 8-9 shows our final context link back to Perl DBI, and the use of regular expressions. This Perl routine's purpose is to list all of the tables in USER_TABLES and to highlight a chosen string ” in this case, COL . Notice the use of _flush , the built-in function that clears out the Perl interpreter beforehand.

Figure 8-9. Callback DBI, using ExtProc context

As Laurence Olivier might have said, we'll leave it to your imagination to fill in the many and varied possibilities of using extproc_perl . Suffice it to say that through extproc_perl you now have the entire range of Perl and CPAN modules to play with ” including mailing, regular expressions, FTP, Telnet, IO::Socket , and all the other golden gems of Perl Internet functionality. If you're interested in encryption and related security operations, you'll find that you now have a full range of Perl security modules available, including Authen::ACE , Crypt::Beowulf , the various message digest algorithms, and the Crypt::Twofish2 encryption module. You can see the ever-growing list of Perl security modules at http://search.cpan.org/Catalog/Security/.