| [ Team LiB ] |
|
1.18 External ProceduresExternal 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 ProcedureThe following are the steps you need to follow in order to create an external procedure. 1.18.1.1 Set up the listenerExternal 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)
)
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 DLLThis 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 databaseCreate 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 procedureThe 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:
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 ParametersWhen 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 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).
 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| [ Team LiB ] |
|
External datatypes