How PL/SQL Calls an External Procedure

To call an external procedure, PL/SQL must know the DLL or shared library in which the procedure resides. PL/SQL looks up the alias library in the EXTERNAL clause of the subprogram that registered the external procedure. The data dictionary is used to determine the actual path to the operating system shared library or DLL.

PL/SQL alerts a Listener process, which in turn spawns (launches) a session-specific agent named extproc. The Listener hands over the connection to extproc. PL/SQL passes to extproc the name of the DLL, the name of the external procedure, and any parameters passed in by the caller.

Then, extproc loads the DLL and runs the external procedure. Also, extproc handles service calls (such as raising an exception) and callbacks to the Oracle server. Finally, extproc passes to PL/SQL any values returned by the external procedure. Figure 4-1 shows the flow of control.

Figure 4-1 How an External Procedure is Called

Note: The Listener must start extproc on the system that runs the Oracle server. Starting extproc on a different system is not supported.

After the external procedure completes, extproc remains active throughout your Oracle session. (When you log off, extproc is killed.) Thus, you incur the cost of spawning extproc only once, no matter how many calls you make. Still, you should call an external procedure only when the computational benefits outweigh the cost.

See Also: For information about administering extproc and external procedure calls, see the Oracle8i Administrator's Guide.