|
Chapter 1 Oracle PL/SQL Language Pocket Reference |
|
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.
The following are the steps you need to follow in order to create an external procedure.
External procedures require a listener. If you are running a Net8 listener, it can be used as the extproc listener as well. See the Oracle8 Administrators' Guide or the Net8 Administrators' Guide for the details on configuring your listener.
This step has nothing to do with PL/SQL or 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 below, we will use the existing random number generating calls available from the operating system.
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';
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, since 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 NT:
CREATE OR REPLACE LIBRARY libc_l AS 'C:\WINNT\SYSTEM32\CRTDLL.DLL';
The syntax for the wrapper procedure is:
CREATE [OR REPLACE] PROCEDURE proc_name [parm_list] AS|IS EXTERNAL LIBRARY library_name [NAME external_name] [LANGUAGE language_name] [CALLING STANDARD C | PASCAL] [WITH CONTEXT] [PARAMETERS (external_parameter_list)];
The following are the syntactic elements defined:
The name of the wrapper procedure.
The name of the library created with the CREATE LIBRARY statement.
The 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.
The language that the external routine was written in. It defaults to C.
The calling standard, which defaults to C. The Pascal calling standard reverses the order of the parameters, and the called routine is responsible for popping the parameters off the stack.
Used to pass a context pointer to the external routine, so it can make OCI calls back to the database.
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 the next section, Section 1.12.3, "Parameters ".
The wrapper PL/SQL function or procedure is usually 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 EXTERNAL -- Keyword to indicate external routine. LIBRARY libc_l -- The library created above. NAME "rand" -- Function name in the library. LANGUAGE C; -- Language of routine. PROCEDURE srand (seed IN PLS_INTEGER) IS EXTERNAL LIBRARY libc_l NAME "srand" -- Name is lowercase in this library. LANGUAGE C PARAMETERS (seed ub4); --Map to unsigned 4byte. 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, see O'Reilly's books Oracle Built-in Packages and Oracle PL/SQL Built-ins Pocket Reference .
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 each parameter in the list is:
CONTEXT | RETURN | parameter_name [property] [BY REFERENCE] [external_datatype]
The keyword CONTEXT indicates the position in the parameter list at which the context pointer will be passed. It is required if the WITH CONTEXT clause is being used to pass a context pointer to the called program. By convention, CONTEXT appears as the first parameter in the external parameter list. If CONTEXT is used, the property, BY REFERENCE, and external_datatype optional sections are invalid.
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 key words 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 Oracle8 syntax:
INDICATOR | LENGTH | MAXLEN | CHARSETID| CHARSETFORM
The general Oracle8 i syntax is:
INDICATOR [STRUCT | TDO ] | LENGTH | MAXLEN | CHARSETID | CHARSETFORM | SELF
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.
In Oracle8 i , 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.
|
|
|
|
| 1.17 Collections | 1.19 Java Language Integration |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.