The SQL statement CREATE LIBRARY defines an alias in the Oracle data dictionary for the external shared library file, allowing the PL/SQL runtime engine to find the library when it is called. The only users who can create libraries are administrators and those to whom they have granted the CREATE LIBRARY or CREATE ANY LIBRARY privilege.
The general syntax for the CREATE LIBRARY command is:
CREATE [ OR REPLACE ] LIBRARY library_name AS ' path_to_file ' [ AGENT ' agent_db_link ' ];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. The library name cannot be the same as a table, top-level PL/SQL object, or anything else in the main namespace.
path_to_file
The fully qualified pathname to the shared object (or DLL) file, enclosed in single quotes.
In Oracle9i, it became possible to use environment variables in path_to_file. In particular, if the operating system-level account sets the variable before starting the listener, you can put this variable in the CREATE LIBRARY statement; for example:
CREATE LIBRARY extprocshell_lib AS '${ORACLE_HOME}/lib/extprocsh.so'; -- UnixCREATE LIBRARY extprocshell_lib AS '%ORACLE_HOME%\bin\extprocsh.dll'; -- MSThis may be a good thing to do for the sake of script portability, although it is unclear to me whether using an environment variable in this fashion is good from a security standpoint.
Another way to make variables available to libraries is to add the variable to the ENVS setting of the external procedure listener. To make a MYLIBS variable available on Unix, you could use:
(ENVS="EXTPROC_DLLS=ANY,MYLIBS=/usr/local/extproclib")Note that a comma separates the DLL path and the environment variable setting. You would therefore create a library using a statement such as:
CREATE LIBRARY extprocshell_lib AS '${MYLIBS}/extprocsh.so'; -- UnixI have confirmed that putting the variable in ENVS and referring to it in the library path does in fact work on Unix systems, but my attempts to use the equivalent on Microsoft Windows XP did not succeed.
One final comment: another variable on Unix systems that you may want to set is LD_LIBRARY_PATH or its equivalent, in order to supply the external procedure a non-default search path for shared libraries it needs to open.
AGENT 'agent_db_link'
Optional database link (Oracle9i and later) to which the library owner has access; the link must be associated with a service name for an external procedure. Using the AGENT clause allows the external procedure to run on a different database server, although it must still be on the same machine.
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). The owner automatically has execution privileges, and can grant and revoke the EXECUTE privilege on the library to other users.
|
|
|
Other users who have received EXECUTE privilege on a library can refer to it in their own call specs using owner.library syntax, or they can create and use synonyms for the library 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. If you have an error in the path, you won't know it until the first time you try to execute the function.
You need to create only a single Oracle library in this fashion for each shared library file you use. There can be any number of callable C functions in the library file, and any number of call specifications that refer to the library.
Let's take a closer look at how to write a PL/SQL subprogram that maps the desired routine from the shared library into a PL/SQL-callable form.
23.4 Writing the Call Specification
An external procedure can serve as the implementation of a program unit other than an anonymous block. In other words, a call specification can appear in a top-level procedure or function, a packaged procedure or function, or an object method. What's more, you can define the call spec in either the specification or the body of packaged program units (or in either the spec or body of object types). Here are some schematic examples:
CREATE FUNCTION name ( args ) RETURN datatype AS callspec ;You should recognize the form shown here as that of the shell() function shown earlier in the chapter. You can also create a procedure:
CREATE PROCEDURE name AS callspec ;In this case, the corresponding C function would be typed void.
The next form shows a packaged function that does not need a package body:
CREATE PACKAGE pkgname AS FUNCTION name RETURN datatype AS callspec ;END;However, when the time comes to modify the package, you would have to recompile the specification. Depending on the change you need to make, you may considerably reduce the recompilation ripple effect by moving the call spec into the package body:
CREATE PACKAGE pkgname AS PROCEDURE name ;END; CREATE PACKAGE BODY pkgname AS PROCEDURE name IS callspec ;END;Unpublished or private program units inside packages can also be implemented as external procedures. Using a call spec in an object type method is quite similar to using it in a package; that is, you can put the call spec in the object type specification or in the corresponding type body.
23.4.1 The Call Spec: Overall Syntax
The AS LANGUAGE clause[2] distinguishes the call spec.
[2] Oracle 8.0 did not have this clause, offering instead a now-deprecated form, AS EXTERNAL.
Syntactically, it looks like this:
AS LANGUAGE C LIBRARY library_name [ NAME external_function_name ] [ WITH CONTEXT ] [ AGENT IN ( formal_parameter_name ) ] [ PARAMETERS ( external_parameter_map ) ];where:
AS LANGUAGE C
Another option here is AS LANGUAGE JAVA, as covered in Chapter 22.
library_name
Name of the library, as defined in a CREATE LIBRARY statement, which you have privilege to execute, either by owning it or by receiving the privilege.
external_function_name
|
|
|
Name of the function as defined in the C language library. If the name is lowercase or mixed case, you must put double quotes around it. You can omit this parameter, in which case the name of the external routine must match your PL/SQL module's name (defaults to uppercase).
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 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 need to manipulate the data structure's content directly; instead, the structure simply facilitates other OCI calls that 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.
AGENT IN (formal_parameter_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. The idea is that you 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.
PARAMETERS (external_parameter_map)
This section gives the position and datatypes of parameters exchanged between PL/SQL and C. The external_parameter_map is a comma-delimited list of elements that match positionally with the parameters in the C function or that supply additional properties.
Getting the mapping right is potentially the most complex task you face, so the next section spends a bit of time examining the wilderness of details.
23.4.2 Parameter Mapping: The Example Revisited
Consider for a moment the problems of exchanging data between PL/SQL and C. PL/SQL has its own set of datatypes that are only somewhat similar to those you find in C.PL/SQL variables can be NULL and subject to three-valued truth table logic; C variables have no equivalent concept. Your C library might not know which national language character set you're using to express alphanumeric values. And should your C functions expect a given argument by value or by reference (pointer)?
I'd like to start with an example that builds on the shell program illustrated earlier in the chapter. When we last saw the shell() function, it had no protection from being called with a NULL argument instead of a real command. It turns out that calling shell(NULL) results in the runtime error ORA-01405: fetched column value is NULL. That may be a perfectly acceptable behavior in some applications, but what if I prefer that the external procedure simply respond to a null input with a null output?
Properly detecting an Oracle NULL in C requires PL/SQL to transmit an additional parameter known as an indicator variable. Likewise, for the C program to return an Oracle NULL, it must return a separate indicator parameter back to PL/SQL. While Oracle sets and interprets this value automatically on the PL/SQL side, the C application will need to get and set this value explicitly.
It's probably simplest to illustrate this situation by looking at how the PL/SQL call spec will change:
CREATE OR REPLACE FUNCTION shell(cmd IN VARCHAR2) RETURN PLS_INTEGERAS LANGUAGE C LIBRARY extprocshell_lib NAME "extprocsh" PARAMETERS (cmd STRING, cmd INDICATOR, RETURN INDICATOR, RETURN INT);Although the PL/SQL function's formal parameters can appear anywhere in the PARAMETERS mapping, the items in the mapping must correspond in position and in associated datatype with the parameters in the C function. Any RETURN mapping that you need to provide must be the last item on the list.
|
|
|
You can omit RETURN from the parameter map if you want Oracle to use the default mapping. This would actually be OK in our case, although the indicator still has to be there:
CREATE OR REPLACE FUNCTION shell(cmd IN VARCHAR2) RETURN PLS_INTEGERAS LANGUAGE C LIBRARY extprocshell_lib NAME "extprocsh" PARAMETERS (cmd STRING, cmd INDICATOR, RETURN INDICATOR);The really good news is that even though we've made a number of changes to the call spec compared with the version earlier in the chapter, a program that invokes the shell() function sees no change in the number or datatype of its parameters.
Let's turn now to the new version of the C program, which adds two parameters, one for each indicator:
1 #include <ociextp.h> 2 3 int extprocsh(char *cmd, short cmdInd, short *retInd) 4 { 5 if (cmdInd == OCI_IND_NOTNULL) 6 { 7 *retInd = (short)OCI_IND_NOTNULL; 8 return system(cmd); 9 } else 10 { 11 *retInd = (short)OCI_IND_NULL; 12 return 0; 13 } 14 }Here are the important lines to note:
Line 1
This include file appears in the %ORACLE_HOME%\oci\include subdirectory on Microsoft platforms; on Unix-like machines, I've spotted this file in $ORACLE_HOME/rdbms/demo, although it may be somewhere else on your system.
Line 3
Notice that the command indicator is short, but the return indicator is short *. That follows the argument-passing convention of using call-by-value for input parameters sent from PL/SQL to C, but call-by-reference for output or return parameters sent from C to PL/SQL.
Lines 5, 7
The indicator variable is either OCI_IND_NULL or OCI_IND_NOTNULL; these are special #define values from Oracle's include file. Here, explicit assignments in the code set the return indicator to be one or the other.
Lines 11-12
The return indicator takes precedence over the return of 0; the latter is simply ignored.
Here are some simple commands that will compile and link the above program, first using Unix (still with GNU C):
gcc -c -I${ORACLE_HOME}/rdbms/demo -I$ORACLE_HOME/rdbms/public extprocsh.cgcc -shared -o extprocsh.so extprocsh.oAnd here are the equivalent commands using the Cygwin port of gcc on a Microsoft operating system:
gcc -I/cygdrive/c/oracle/ora92/oci/include/ -c extprocsh.cgcc -shared -o extprocsh.dll extprocsh.oAnd now, steel yourself to face the intimidating details of parameter mapping.
23.4.3 Parameter Mapping: The Full Story
As shown in the previous section, 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
You identify an external datatype in the PARAMETERS clause with a keyword known to PL/SQL. In some cases, the external datatypes have the same name as the C type, but in others they don't. For example, 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 type uses the STRING external datatype, which normally maps to a char * in C.
Table 23-1 lists all 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, as the previous example illustrated. The defaults, if ambiguous, are shown in bold in the table.
| Table 23-1. Legal mappings of PL/SQL and C datatypes | |||
| Datatype of PL/SQL parameter | PL/SQL keyword identifying external type | C datatypes for PL/SQL parameters that are IN or function return values | C datatypes for PL/SQL parameters that are IN OUT, OUT, or any parameter designated as being passed BY REFERENCE |
| Long integer family: BINARY_INTEGER, BOOLEAN, PLS_INTEGER | INT, UNSIGNED INT, CHAR, UNSIGNED CHAR, SHORT, UNSIGNED SHORT, LONG, UNSIGNED LONG, SB1, UB1, SB2, UB2, SB4, UB4, SIZE_T | int, unsigned int, char, unsigned char, short, unsigned short, long, unsigned long, sb1, ub1, sb2, ub2, sb4, ub4, size_t | Same list of types as at left, but use a pointer (for example, the default is int * rather than int) |
| Short integer family: NATURAL, NATURALN, POSITIVE, POSITIVEN, SIGNTYPE | Same as above, except default is UNSIGNED INT | Same as above, except default is unsigned int | Same as above, except default is unsigned int * |
| Character family: VARCHAR2, CHAR, NCHAR, LONG, NVARCHAR2, VARCHAR, CHARACTER, ROWID | STRING, OCISTRING | char *,OCIString * | char *,OCIString * |
| NUMBER | OCINUMBER | OCINumber * | OCINumber * |
| DOUBLE PRECISION | DOUBLE | double | double * |
| FLOAT, REAL | FLOAT | float | float * |
| RAW, LONG RAW | RAW, OCIRAW | unsigned char *, OCIRaw * | unsigned char *, OCIRaw * |
| DATE | OCIDATE | OCIDate * | OCIDate * |
| Timestamp family: TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE | OCIDATETIME | OCIDateTime * | OCIDateTime * |
| INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH | OCIINTERVAL | OCIInterval * | OCIInterval * |
| BFILE, BLOB, CLOB | OCILOBLOCATOR | OCILOBLOCATOR * | OCILOBLOCATOR ** |
| Descriptor of user-defined type (collection or object) | TDO | OCIType * | OCIType * |
| Value of user-defined collection | OCICOLL | OCIColl **, OCIArray **, OCITable ** | OCIColl **, OCIArray **, OCITable ** |
| Value of user-defined object | DVOID | dvoid * | dvoid * for final types; dvoid ** for nonfinal types |
In some simple cases where you are passing only numeric arguments and where the defaults are acceptable, you can actually omit the PARAMETERS clause entirely. However, you must use it when you want to pass indicators or other data properties.
|
|
|
Each piece of supplemental information we want to exchange will be passed as a separate parameter, and will appear both in the PARAMETERS clause and in the C language function specification.
23.4.4 More Syntax: The PARAMETERS Clause
The PARAMETERS clause provides a comma-delimited list that may contain five different kinds of elements:
The name of the parameter followed by the external datatype identifier
The keyword RETURN and its associated external datatype identifier
A "property" of the PL/SQL parameter or return value, such as a nullness indicator or an integer corresponding to its length
The keyword CONTEXT, which is a placeholder for the context pointer
The keyword SELF, in the case of an external procedure for an object type member method
Elements (other than CONTEXT) follow the syntax pattern:
{ pname | RETURN | SELF } [ property ] [ BY REFERENCE ] [ external_datatype ]If your call spec includes WITH CONTEXT, the corresponding element in the parameter list is simply:
CONTEXTBy convention, if you have specified WITH CONTEXT, you should make CONTEXT the first argument because that is its default location if the rest of the parameter mappings are defaulted.
Parameter entries have the following meanings:
pname | RETURN | SELF
The name of the parameter as specified in the formal parameter list of the PL/SQL module, or the keyword RETURN, or the keyword SELF (in the case of a member method in an object type). PL/SQL parameter names are not necessarily the names of formal parameters in the C language routine. However, parameters in the PL/SQL parameter list must match one for one, in order, those in the C language specification.
property
One of the following: INDICATOR, INDICATOR STRUCT, LENGTH, MAXLEN, TDO, CHARSETID, or CHARSETFORM. These are described in the next section.
BY REFERENCE
Pass the parameter by reference. In other words, the module in the shared library is expecting a pointer to the parameter rather than its value. BY REFERENCE only has meaning for scalar IN parameters that are not strings, such as BINARY_INTEGER, PLS_INTEGER, FLOAT, DOUBLE PRECISION, and REAL. All others (IN OUT and OUT parameters, as well as IN parameters of type STRING) are always passed by reference, and the corresponding C prototype must specify a pointer.
external_datatype
The external datatype keyword from the second column of Table 23-1. If this is omitted, the external datatype will default as indicated in the table.
