Registering an External Procedure

To call an external procedure, you must not only tell PL/SQL the alias library in which to find the external procedure, but also how to call the procedure and what arguments to pass to it.

Earlier, the type DataStream was defined, and certain methods of type DataStream were defined by calling functions from a package DS_Package. Also, this package was specified. The following statement defines the body of this package (DS_Package).

CREATE OR REPLACE PACKAGE BODY DS_Package AS 
     FUNCTION DS_Findmin(data  CLOB) RETURN PLS_INTEGER IS EXTERNAL 
     NAME "c_findmin" LIBRARY DS_Lib LANGUAGE C WITH CONTEXT; 
     FUNCTION DS_Findmax(data CLOB) RETURN PLS_INTEGER IS EXTERNAL 
     NAME "c_findmax" LIBRARY DS_Lib LANGUAGE C WITH CONTEXT; 
   END;

In the PACKAGE BODY declaration clause of this example, the package functions are tied to external procedures in a shared library. The EXTERNAL clause in the function declaration registers information about the external procedure, such as its name (found after the NAME keyword), its location (which must be an alias library, following the LIBRARY keyword), the language in which the external procedure is written (following the LANGUAGE keyword), and so on. For a description of the parameters that can accompany an EXTERNAL clause, see the PL/SQL User's Guide and Reference.

Note: In Oracle8i, only C is supported as a language for external procedures. However, it is easy to call routines in other languages, such as C++, by dispatching from the server as C but coding the external procedures as extern "C". Thus, by creating C wrappers, you can easily call external procedures written in other languages.

The final part of the EXTERNAL clause in the example is the WITH CONTEXT specification. This means that a context pointer is passed to the external procedure. The context pointer is opaque to the external procedure, but is available so that the external procedure can call back to the Oracle8i server, to potentially access more data in the same transaction context. The WITH CONTEXT clause is discussed in "Using the WITH CONTEXT Clause".

Although the example describes external procedure calls from object type methods, a data cartridge can use external procedures from a variety of other places in PL/SQL. External procedure calls can appear in:

• Anonymous blocks

• Standalone and packaged subprograms

• Methods of an object type

• Database triggers

• SQL statements (calls to packaged functions only)