1.18.1.1 Set up the listener

External procedures require a listener. If you are running a Net8 listener, it can be used as the extproc listener as well. See the Oracle8 Administrators' Guide or the Net8 Administrators' Guide for the details on configuring your listener.

1.18.1.2 Identify or create the shared library or DLL

This step has nothing to do with PL/SQL or the database. You must write your own C routines and link them into a shared library/DLL or use an existing library's functions or procedures. In the simple example below, we will use the existing random number generating calls available from the operating system.

1.18.1.3 Create the library in the database

Create a library in the database for the shared library or DLL using the CREATE LIBRARY statement:

CREATE [OR REPLACE] LIBRARY library_name IS | AS 
	'absolute_path_and_file';

To remove libraries from the database, you use the DROP LIBRARY statement:

DROP LIBRARY library_name;

To call out to the C runtime library's rand function, you don't have to code any C routines at all, since the call is already linked into a shared library, and because its arguments are directly type-mappable to PL/SQL. If the rand function is in the standard /lib/libc.so shared library, as on Solaris, you would issue the following CREATE LIBRARY statement:

CREATE OR REPLACE LIBRARY libc_l AS 
   '/lib/libc.so';  -- References C runtime library.

This is the typical corresponding statement for NT:

CREATE OR REPLACE LIBRARY libc_l AS
   'C:\WINNT\SYSTEM32\CRTDLL.DLL';

1.18.1.4 Create the PL/SQL wrapper for the external procedure

The syntax for the wrapper procedure is:

CREATE [OR REPLACE] PROCEDURE proc_name 
   [parm_list]
AS|IS EXTERNAL
   LIBRARY library_name
   [NAME external_name]
   [LANGUAGE language_name]
   [CALLING STANDARD  C | PASCAL]
   [WITH CONTEXT]
   [PARAMETERS (external_parameter_list)];

The following are the syntactic elements defined:

proc_name

The name of the wrapper procedure.

library_name

The name of the library created with the CREATE LIBRARY statement.

external_name

The name of the external routine as it appears in the library. It defaults to the wrapper package name. PL/SQL package names are usually saved in uppercase, so the external_name may need to be enclosed in double quotes to preserve case.

language_name

The language that the external routine was written in. It defaults to C.

CALLING STANDARD

The calling standard, which defaults to C. The Pascal calling standard reverses the order of the parameters, and the called routine is responsible for popping the parameters off the stack.

WITH CONTEXT

Used to pass a context pointer to the external routine, so it can make OCI calls back to the database.

PARAMETERS

Identify the external_parameter_list, which is a comma-delimited list containing the position and datatype of parameters that get passed to the external routine. For more details on the external_parameter_list, see the next section, Section 1.12.3, "Parameters".

The wrapper PL/SQL function or procedure is usually in a package. Using the preceding random number generator example, we could create the wrapper package as follows:

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;

CREATE PACKAGE BODY random_utl
AS
   FUNCTION rand RETURN PLS_INTEGER
   IS
      EXTERNAL       -- Keyword to indicate external 
                        routine.
      LIBRARY libc_l -- The library created above.
      NAME "rand"    -- Function name in the 
                        library.
      LANGUAGE C;    -- Language of routine.

   PROCEDURE srand (seed IN PLS_INTEGER)
   IS
      EXTERNAL LIBRARY libc_l
      NAME "srand"   -- Name is lowercase in this 
                        library.
      LANGUAGE C
      PARAMETERS (seed ub4); --Map to unsigned 
                               4byte. 
INT
END random_utl;

To use this external random number function, we simply call the package procedure srand to seed the generator, then the package function rand to get random numbers:

DECLARE
   random_nbr  PLS_INTEGER;
   seed        PLS_INTEGER;
BEGIN
   SELECT TO_CHAR(SYSDATE,'SSSSS') INTO seed 
      FROM dual;

   random_utl.srand(seed);  -- Seed the generator.

   random_nbr := random_utl.rand; -- Get the number.
   DBMS_OUTPUT.PUT_LINE('number='||random_nbr);

   random_nbr := random_utl.rand; -- Get the number.
   DBMS_OUTPUT.PUT_LINE('number='||random_nbr);
END;

You can generate random numbers without the complexity or overhead of an external call by using the built-in package DBMS_RANDOM. To learn more about DBMS_RANDOM and other built-ins, see O'Reilly's books Oracle Built-in Packages and Oracle PL/SQL Built-ins Pocket Reference.