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 Oracle9i) 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

Oracle9i Application Developer's Guide — Fundamentals for Release 1, Chapter 10.

http://download.oracle.com/otndoc/oracle9i/901_doc/server.901/a90117/manproc.htm

Oracle9i 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:

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:

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:

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):

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:

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:

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($1)/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:

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/.