21.2.1 Step 1: Set Up the Listener

What actually happens when your code needs to use the external procedure? First, your code calls a predefined PL/SQL body. When the PL/SQL runtime engine notices such a call, it looks for the special Net8 listener named EXTERNAL_PROCEDURE_LISTENER, which in turn spawns a session-specific process called extproc. It is extproc that invokes your routine in the shared library.

You need to create a new listener with a specific name, EXTERNAL_PROCEDURE _LISTENER. This listener process will execute alongside other listener(s) that you already have running.

NOTE: You cannot change the names EXTERNAL_PROCEDURE_LISTENER or extproc. In the listener.ora fragment given, everything that's not in lowercase italics must match the listing.

In your listener.ora file, you will need the following entries:

/* filename on companion disk: lsnrfrag.ora /*
EXTERNAL_PROCEDURE_LISTENER =
    (ADDRESS_LIST =
        (ADDRESS =
            (PROTOCOL=IPC)
            (KEY=epsid)
        )
    )

SID_LIST_EXTERNAL_PROCEDURE_LISTENER =
    (SID_LIST =
        (SID_DESC=
            (SID_NAME=epsid)
            (ORACLE_HOME=full_directory_path)
            (PROGRAM=extproc)
        )
    )

Where:

epsid

A short identifier that is used by Net8 to locate the listener. Its actual name is rather arbitrary, since your programs will never see it. epsid has to be the same identifier in the address list and in the SID list.

full_directory_path

The full pathname to your ORACLE_HOME directory, such as /u01/app/oracle/product/8.0.3 on UNIX or C:\ORANT on Windows NT. Notice that there are no quotes around the directory name.

NOTE: If desired, listener.ora can point the listener's log into your desired directory:

LOG_DIRECTORY_EXTERNAL_PROCEDURE_LISTENER=/u01/app/
oracle/admin/SID/logbook

And, for debugging, you can control "tracing" for your new listener with entries like this:

TRACE_DIRECTORY_EXTERNAL_PROCEDURE_LISTENER=/u01/app/
oracle/admin/SID/logbook
TRACE_LEVEL_EXTERNAL_PROCEDURE_LISTENER=user

The tnsnames.ora file on the machine where the server is running will need an entry like the following:

EXTPROC_CONNECTION_DATA =
   (DESCRIPTION =
      (ADDRESS =
         (PROTOCOL=IPC)
         (KEY=epsid)
      )
      (CONNECT_DATA=
         (SID=epsid))
      )
   )

Again, epsid must match the key used in the listener.ora file.

TIP: If you intend to use external procedures on your server, remember to modify your database startup and shutdown procedures to incorporate the listener start and stop commands. On UNIX, this typically means editing a startup shell script.

On Windows NT, a Net8 listener is automatically installed as a system service the first time you start it from the command line. However, the listener will not launch automatically on boot unless you configure it to do so. You can use the control panel to designate which listeners launch on system startup.

To remove the external procedure service on NT, delete the entries from listener.ora and tnsnames.ora, and use the NT command instsrv <service name> remove as follows:

instsrv OracleTNSListener80external_procedure_listener remove

(The instsrv utility is available in the NT 4.0 Server Resource Kit. Without it, you'll probably have to edit the registry.)

After making these configuration file changes, you'll need to start the new listener process. In UNIX, this is typically performed from the command line:

lsnrctl start external_procedure_listener

If your database server is running on Windows NT, the first time you start the listener, you'll use the LSNRCTL80 command. From the command prompt, for example, the command would be:

LSNRCTL80 start external_procedure_listener

Thereafter (on NT) you can start and stop specific listeners from the Services component of the Control Panel.

21.2.2 Step 2: Identify or Create the Shared Library

Step 1 is completely independent of any external procedure that you may create on your system. The remaining steps, while specific to our random number procedure, include discussion that applies to almost any external procedure.

Although later examples will show how to create your own shared libraries, let's start with an example that requires no C language programming: calling a standard C library function, rand, which generates a 16-bit random number. On many platforms, rand already exists in a shared object library; on UNIX, it's often in /lib/libc.so, and on NT, in c:\winnt\system32\CRTDLL.DLL.[5]

[5] Documentation on available library functions is commonly available in UNIX man pages or in SDK documentation on other platforms.

A bit of background is in order for folks who haven't played with random number generators. Random number algorithms have certain quirks you need to realize. First, such generators can be deterministic; that is, the algorithm may return the same sequence of "random numbers" every time unless first "seeded" with a quasi-random number. Often, the previous random number is stored and used as a seed. But for the very first call, your program provides the seed, perhaps using some function of the current system time. If you call rand later with an identical seed value, you will get an identical pseudo-random sequence.

rand has a companion "seeding" function, srand, that allows you to supply your own seed value. Calling srand before calling rand stores the seed value as a global in memory, for use by the next call to rand. Subsequent calls from the same session need not call srand, since rand will re-seed itself.

21.2.3 Step 3: Issue CREATE LIBRARY Statement

Now that we have identified /lib/libc.so as the file containing our needed functions, the next thing we have to do is create a library in the Oracle database. This is the easy part!

Creating a library is a way of telling Oracle that we want to refer to a specific shared object file by a programmer-defined name. For many UNIX systems, /lib/libc.so will contain the needed function as shown here:

CREATE OR REPLACE LIBRARY libc_l
AS
   '/lib/libc.so';

Executing this command requires the CREATE LIBRARY privilege (see Section 21.3, "Syntax for External Procedures" for more details).

Note that we have to use a fully qualified pathname; attempting to use an environment variable such as $ORACLE_BASE in the filename will not work.

If your database server runs on Windows NT, your library would likely be created as follows:

CREATE OR REPLACE LIBRARY libc_l
AS
   'c:\winnt\system32\CRTDLL.DLL';

Regardless of platform, you only need to create a single library in this fashion for each shared object file you use. That is, even though you have only issued a single CREATE LIBRARY command for libc.so (or CRTDLL.DLL), you can define any number of external procedures that use routines from that file.

21.2.4 Step 4: Create the PL/SQL Body

The final step is to create a function or procedure definition which registers the desired routine from the shared library. This feature lets you write the body of a PL/SQL procedure or function in C instead of PL/SQL. To the caller it looks like any other PL/SQL subprogram.

Assuming that your C language skills are ready for any custom programming needed in Step 3, Step 4 is potentially the most complex one. Because of the differences between PL/SQL arguments and C language arguments (in datatype, character set, whether they can be null, etc.), Oracle provides a lot of "instrumentation" to allow you to properly map PL/SQL arguments to C language arguments. The details of this instrumentation are described in Section 21.4, "Mapping Parameters" later in this chapter.

TIP: All of the samples in this chapter put the needed modules in a PL/SQL package. One of many benefits of packages is that they enable us to use the RESTRICT_REFERENCES pragma with our module(s). This pragma allows us to tell Oracle that the user-defined function is "safe" and can therefore be used in SQL statements. (See Chapter 17, Calling PL/SQL Functions in SQL, for a full discussion of this pragma.)

Returning once again to our random number example, the specification for an appropriate PL/SQL package might look like this:

/* Filename on companion disk: rand_utl.sql */
CREATE OR REPLACE PACKAGE random_utl
AS
    FUNCTION rand RETURN PLS_INTEGER;
    PRAGMA RESTRICT_REFERENCES (rand, WNDS, RNDS, WNPS, RNPS);

    PROCEDURE srand (seed IN PLS_INTEGER);
    PRAGMA RESTRICT_REFERENCES (srand, WNDS, RNDS, WNPS, RNPS);

END random_utl;

Notice that the package specification is completely devoid of clues that we intend to implement the two subprograms as external procedures. We can't yet tell that rand and srand are any different from conventional PL/SQL modules. And that is exactly the point! From a usage perspective, external procedures are interchangeable with conventional procedures.

Our package body is blissfully short. By the way, assuming that your library is defined correctly, this package will work as-is on either UNIX or Windows NT. (Even in cases where you can't use the same external code on different operating systems, it may be possible to make the PL/SQL specification the same. You could then make the external code the only thing that differs -- which would be very desirable if you have to support multiple platforms.)

CREATE OR REPLACE PACKAGE BODY random_utl
AS
   /* Tested with: (1) Solaris 2.5.1 and Oracle 8.0.3
   ||              (2) Windows NT 4.0 and Oracle 8.0.3
   */

   FUNCTION rand RETURN PLS_INTEGER
   /* Return a random number between 1 and (2**16 - 1), using
   || the current seed value.
   */
   IS
      EXTERNAL       -- tell PL/SQL that this is an external procedure
      LIBRARY libc_l -- specify the library that we created above
      NAME "rand"    -- function's real name is lowercase
      LANGUAGE C;    -- we are calling a function written in C

   PROCEDURE srand (seed IN PLS_INTEGER)
   /* Store a seed value used by external rand() function */
   IS
      EXTERNAL
      LIBRARY libc_l
      NAME "srand"   -- srand (lowercase) is function's real name
      LANGUAGE C
      PARAMETERS (seed ub4);  -- map to unsigned four-byte integer
END random_utl;

In this example, we have chosen to make the names of the PL/SQL modules identical to those in the shared object library. It is not necessary that they match; in fact, you may wish to make them different so that you can talk about (or document) the parameters independently.

Notice the PARAMETERS clause in the body of srand. Each of the formal parameters to the PL/SQL module must have at least one corresponding entry in a PARAMETERS clause. Although there is an extensive set of defaults which can eliminate the need for this clause, this explicit PARAMETERS clause makes it perfectly clear how the parameters will be mapped between PL/SQL and C.

21.2.5 Using the rand External Procedure

Now that we've "registered" the external procedure with a PL/SQL package, we can test it:

SET SERVEROUTPUT ON SIZE 100000
DECLARE
   rnd_value   PLS_INTEGER;
   seed        PLS_INTEGER;
BEGIN
   /* Generate a seed value from the current system time. */
   SELECT TO_CHAR(SYSDATE, 'SSSSS') INTO seed FROM DUAL;

   /* Call the srand external procedure to store our seed in memory. */
   random_utl.srand (seed);

   /* Now demonstrate some random numbers. */
   FOR v_cnt IN 1 .. 10 LOOP
      rnd_value := random_utl.rand;
      DBMS_OUTPUT.PUT_LINE ('rand() call #' || v_cnt ||
                            ' returns ' || rnd_value);
   END LOOP;
END;

This brief test routine simply seeds the library routine with a quasi-random number derived from the current system time, then calls the random number generator ten times in a row.

One of our trial runs produced the following results:

rand() call #1 returns 27610
rand() call #2 returns 27964
rand() call #3 returns 27908
rand() call #4 returns 21610
rand() call #5 returns 14085
rand() call #6 returns 14281
rand() call #7 returns 9569
rand() call #8 returns 9397
rand() call #9 returns 24266
rand() call #10 returns 142