Oracle PL/SQL Language Pocket Reference, Second Edition

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 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)
   )

extprocKey

Short identifier used by Oracle Net to distinguish this listener from other potential IPC listeners. Its actual name is arbitrary, ...