1.18 External Procedures

External procedures provide a mechanism for calling out to a non-database program, such as a DLL under NT or a shared library under Unix. Every session calling an external procedure will have its own extproc process started by the listener. This extproc process is started with the first call to the external procedure and terminates when the session exits. The shared library needs to have a corresponding library created for it in the database.

1.18.1 Creating an External Procedure

The following are the steps you need to follow in order to create an external procedure.

1.18.1.1 Set up the listener

External procedures require a listener. If you are running an Oracle Net database listener, it can be used as the extproc listener as well, although you may increase security by separating it from the external procedure listener and launching it from a privilege-limited account. Here is one way to structure the listener.ora file:

LISTENER =
   (ADDRESS = (PROTOCOL=TCP)(HOST=hostname)(PORT=1521))

EXTPROC_LISTENER =
   (ADDRESS = (PROTOCOL = IPC)(KEY = extprocKey))

SID_LIST_LISTENER =
   (SID_DESC =
      (GLOBAL_DBNAME = global_name)
      (ORACLE_HOME = oracle_home_directory)
      (SID_NAME = SID)
   )

SID_LIST_EXTPROC_LISTENER =
   (SID_DESC =
      (SID_NAME = extprocSID)
      (ORACLE_HOME = oracle_home_directory)
      (ENVS = "EXTPROC_DLLS=
        qualifier:shared_object_file_list")
      (PROGRAM = extproc)
   )

extprocKey

Short identifier used by Oracle Net to distinguish this listener from other potential IPC listeners. Its actual name is arbitrary, because your programs will never see it. Oracle uses EXTPROC0 as the default name for the first Oracle Net installation on a given machine. This identifier must be the same in the address list of the listener.ora and tnsnames.ora files.

extprocSID

Arbitrary unique identifier for the external procedure listener. In the default installation, Oracle uses the value PLSExtProc.

ENVS

Means of passing environment variables to the external procedure listener. The example above shows only one name/value pair, but any number of pairs are permitted. Use name=value syntax, separating each name/value pair with a comma, as in

(ENVS="LD_LIBRARY_PATH=
   /lib:/oracle/product/9.2/lib,EXTPROC_DLLS=ANY")

EXTPROC_DLLS

Environment variable designating non-default locations of shared libraries/DLLs. Without this setting, the default security settings of Oracle9i Release 2 require the library/DLL to be in the bin subdirectory on Windows platforms, and in Oracle's lib subdirectory on Unix. The qualifier is actually optional; if it is not present, the additional files given in a colon-delimited shared_object_file_list are allowed. If qualifier is present, it must be one of the keywords ALL (no location checking) or ONLY (disallows the default locations).

Here is an example ENVS entry supporting two shared libraries found in non-default locations:

(ENVS="EXTPROC_DLLS=ONLY:/u01/app/oracle/admin/local/lib/ 
   extprocsh.so:/u01/app/oracle/admin/local/lib/
   RawdataToPrinter.so")

Installations unconcerned with security may wish to permit any location using an entry such as the following:

(ENVS="EXTPROC_DLLS=ALL")

See the Oracle9i Application Developers Guide - Fundamentals or the Oracle9i Net Services Administrators Guide for more details on configuring external procedures and your listener.

1.18.1.2 Identify or create the shared library or DLL

This step has nothing to do with PL/SQL and may or may not have anything to do with 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 in the next section, 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'
   [ AGENT 'agent_db_link'];

The optional AGENT clause represents a database link associated with the service name of an external procedure listener. In this way the library can invoke a separate runtime instantiation of the extproc process. This process can run on a different database server, although that server must still reside on the same machine as the calling program.

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, because 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 Microsoft Windows:

CREATE OR REPLACE LIBRARY libc_l AS
   'C:\WINDOWS\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 } LANGUAGE C
   [NAME external_name]   LIBRARY library_name
   [ AGENT IN (formal_parameter_name) ]
   [WITH CONTEXT]
   [PARAMETERS (external_parameter_list)];

where:

proc_name

Name of the wrapper procedure.

library_name

Name of the library created with the CREATE LIBRARY statement.

agent_name

This clause is a way of designating a different agent process, similar to the AGENT clause on the library, but deferring the selection of the agent until runtime. You will pass in the value of the agent as a formal PL/SQL parameter to the call spec; it will supersede the name of the agent given in the library, if any.

external_name

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.

WITH CONTEXT

Used to pass a context pointer to the external routine, so it can make Oracle Call Interface (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 Section 1.18.2.

The wrapper PL/SQL function or procedure is often 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
      LANGUAGE C      -- Language of routine.
      NAME "rand"     -- Function name in the
      LIBRARY libc_l; -- The library created above.

   PROCEDURE srand (seed IN PLS_INTEGER)
   IS
      LANGUAGE C
      NAME "srand"   -- Name is lowercase in this
      LIBRARY libc_l
      PARAMETERS (seed ub4); --Map to unsigned 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, check out Oracle Built-in Packages.

1.18.2 Parameters

When it comes to passing PL/SQL variables to C variables, we encounter many inconsistencies. For example, PL/SQL supports nullity, while C does not; PL/SQL can have variables in different character sets, while C cannot; and the datatypes in PL/SQL do not directly map to C datatypes.

The PARAMETERS clause specifies the external parameter list, a comma-delimited list containing parameters. The syntax for these parameters (other than CONTEXT) is:

{ pname | RETURN | SELF } [ property ] [ BY REFERENCE ]
   [ external_datatype ]

If your call spec includes WITH CONTEXT, the corresponding element in the parameter list is simply:

CONTEXT

The keyword CONTEXT indicates the position in the parameter list at which the context pointer will be passed. By convention, CONTEXT appears as the first parameter in the external parameter list.

The keyword RETURN indicates that the descriptions are for the return value from the external routine. By default, RETURN is passed by value. You can use the keywords BY REFERENCE to pass by reference (use pointers).

parameter_name is a PL/SQL formal parameter name. By default, IN formal parameters are passed by value. You can use the keywords BY REFERENCE to pass by reference (as a pointer). IN OUT and OUT formal parameters are always passed by reference.

property breaks out further to the general syntax:

INDICATOR | INDICATOR STRUCT | LENGTH | MAXLEN | TDO | 
CHARSETID | CHARSETFORM

INDICATOR indicates whether the corresponding parameter is NULL. In the C program, if the indicator equals the constant OCI_IND_NULL, the parameter is NULL. If the indicator equals the constant OCI_IND_NOTNULL, the indicator is not NULL. For IN parameters, INDICATOR is passed by value (by default). For IN OUT, OUT, and RETURN parameters, INDICATOR is passed by reference.

You can pass a user-defined type to an external procedure. To do so, you will typically pass three parameters: the actual object value; a TDO (Type Descriptor Object) parameter as defined in C by the Oracle Type Translator; and an INDICATOR STRUCT parameter, to designate whether the object is NULL.

LENGTH and MAXLEN can be used to pass the current and maximum length of strings or RAWs. For IN parameters, LENGTH is passed by value (by default). For IN OUT, OUT, and RETURN parameters, LENGTH is passed by reference. MAXLEN is not valid for IN parameters. For IN OUT, OUT, and RETURN parameters, MAXLEN is passed by reference and is read-only.

CHARSETID and CHARSETFORM are used to support NLS character sets. They are the same as the OCI attributes OCI_ATTR_CHARSET_ID and OCI_ATTR_CHARSET_FORM. For IN parameters, CHARSETID and CHARSETFORM are passed by value (by default) and are read-only. For IN OUT, OUT, and RETURN parameters, CHARSETID and CHARSETFORM are passed by reference and are read-only.

SELF is used if an object member function is implemented as a callout instead of a PL/SQL routine.

When moving data between PL/SQL and C, each PL/SQL datatype maps to an "external datatype," identified by a PL/SQL keyword, which in turn maps to an allowed set of C types:

• PL/SQL types External datatypes C types

PL/SQL includes a special set of keywords to use as the external datatype in the PARAMETERS clause. In some cases, the external datatypes have the same name as the C types. If you pass a PL/SQL variable of type PLS_INTEGER, the corresponding default external type is INT, which maps to an int in C. But Oracle's VARCHAR2 uses the STRING external datatype, which normally maps to a char * in C.

The following table lists all of the possible datatype conversions supported by Oracle's PL/SQL-to-C interface. Note that the allowable conversions depend on both the datatype and the mode of the PL/SQL formal parameter. Default mappings are shown in bold (if ambiguous).