Oracle PL/SQL Language Pocket Reference

External Procedures

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.

Creating an External Procedure

The following are the steps you need to follow in order to create an external procedure.

Set up the listener

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.

Identify or create the shared library or DLL

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 the library in the database

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; ...