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 [ OR REPLACE ] LIBRARY <library name> AS '<path to file>';
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.
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 LIBRARY <library name> [ NAME <external routine name> ] [ LANGUAGE <language name> ] [ CALLING STANDARD C | PASCAL ] [ PARAMETERS (<external parameter list>) ] [ WITH CONTEXT ];
Name defined previously in the CREATE LIBRARY statement.
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).
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.
On Windows NT, 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.
This section gives the position and datatypes of parameters exchanged between PL/SQL and C.
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.
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 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).
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.
|This HTML Help has been published using the chm2web software.