Before you try external procedures, be sure that the machine where you're running the Oracle server supports shared or dynamically linked libraries. Virtually all UNIX machines qualify, as do Windows NT machines. If your own machine doesn't qualify, you can stop here, or you can investigate the use of distributed external procedures.
These are your next tasks:
And that's it! Let's look at each step in more detail, focusing on the implementation of a random number generator for PL/SQL.
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.
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) ) )
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.
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
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 ]
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.
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.
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.
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.
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
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.