Syntax for External Procedures

Now that we've gone through the basic steps and seen examples of the kind of code you must write in order to take advantage of external procedures, let's explore each syntactic element in more detail.

CREATE LIBRARY: Creating the External Procedure Library

Step 3 in the random number generator example we presented in the previous section uses the SQL statement CREATE LIBRARY. The general syntax for this command is:

CREATE [ OR REPLACE ] LIBRARY <library name> AS '<path to file>';

Where:

• library name
• A legal PL/SQL identifier. This name will be used in subsequent bodies of external procedures that need to call the shared object (or DLL) file.
• path to file
• The fully qualified pathname to the shared object (or DLL) file. As shown above, it must be enclosed in single quotes.

Here are some things to keep in mind when issuing a CREATE LIBRARY statement:

• The statement must be executed by the DBA or by a user who has been granted CREATE LIBRARY or CREATE ANY LIBRARY privileges.
• As with most other database objects, libraries are owned by a specific Oracle user (schema). Other users can refer to the library using owner.library syntax, or they can create synonyms for the library (or use a synonym) if desired.
• Oracle doesn't check whether the named shared library file exists when you execute the CREATE LIBRARY statement. Nor will it check when you later create an external procedure declaration for a function in that library (see Step 4). If you have a syntax error in the path, you won't know it until the first time you try to execute the function.
• Setting up a listener is typically a DBA task performed (in UNIX, anyway) when the DBA is logged on as the "oracle user." However, for security reasons, Oracle recommends setting up a different OS-level user with a limited profile to run the listener for external procedures.

EXTERNAL: Creating the PL/SQL Body

In lieu of a BEGIN..END block, the body of your PL/SQL function or procedure must contain an EXTERNAL clause. This section describes the content of this clause. Syntactically, it looks like this:

EXTERNAL LIBRARY <library name> [ NAME <external routine name> ] [ LANGUAGE <language name> ] [ CALLING STANDARD C | PASCAL ] [ PARAMETERS (<external parameter list>) ] [ WITH CONTEXT ];

Where:

• library name
• Name defined previously in the CREATE LIBRARY statement.
• external routine name
• Name of the function as defined in the C language library. If the name is lowercase, you must put double quotes around it. You can omit this parameter; if you do, the name of the external routine must match your PL/SQL module's name (defaults to uppercase).
• language name
• Lets PL/SQL know the language in which the external routine is written. In early releases of Oracle8, this parameter can only be C, which is the default.
• CALLING STANDARD
• On Windows, your application may use the Pascal calling standard, which means that arguments are "reversed" on the stack and that your called function will deal with them accordingly. CALLING STANDARD defaults to C if omitted.
• PARAMETERS
• This section gives the position and datatypes of parameters exchanged between PL/SQL and C.
• external parameter list
• External parameters appear in a comma-delimited list. Each item in the list is one of three things:
  • The keyword CONTEXT, which is a placeholder for the context pointer
  • Information about a formal parameter's mapping between PL/SQL and C
  • The keyword RETURN and information about its mapping

  The syntax and rules are discussed in "Mapping Parameters" below.

• WITH CONTEXT
• The presence of this clause indicates that you want PL/SQL to pass a "context pointer" to the called program. The called program must be expecting the pointer as a formal parameter of type OCIExtProcContext * (defined in the C header file ociextp.h).

  This "context" that we are passing via a pointer is a data structure that contains a variety of Oracle-specific information. The called procedure doesn't actually refer directly to the data structure's content; instead, the structure simply facilitates other Oracle Call Interface (OCI) calls which perform various Oracle-specific tasks. These tasks include raising predefined or user-defined exceptions, allocating session-only memory (which gets released as soon as control returns to PL/SQL), and obtaining information about the Oracle user's environment.

DROP: Dropping Libraries

The syntax for dropping a library is simply:

DROP LIBRARY <library name>;

The Oracle user who executes this command must have the DROP LIBRARY or DROP ANY LIBRARY privilege.

Oracle does not check dependency information before dropping the library. This is useful if you need to change the name or location of the shared object file to which the library points. You can just drop it and rebuild it, and any dependent routines will continue to function. (More useful, perhaps, would be a requirement that you use a DROP LIBRARY FORCE command, but such an option does not exist).

Before you drop the library permanently, you'll probably want to look in the DBA_DEPENDENCIES view to see if any PL/SQL module relies on the library.