René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback

External Procedures


The database initiates the call to an external procedure through the listener and the external procedure agent. The database stores the library alias and the call specification (count, dataypes of parameters and so on).
The listener waits for external procedure requests and then starts the external procedure agent process (at most one per session).
The external procedure agent receives calling information (location and name of shared library (dll), parameters and so on) from the database and calls the corresponding function in the shared library. This external procedure agent is the extproc(.exe) executable in the ORACLE_HOME/bin directory.
The shared library contains the function to be called.
The library alias defines the location of the shared library within the database and is used in PL/SQL call specifications.

Sequence of events

1: The user invokes call specification that correspondents to an external procedure.
2: The database finds the name of the shared library and the name of the desired function through the call specification.
3: The database looks up the adress of the external procedure listener in the tnsnames.ora file under the service name extproc_connection_data.
This third step is only performed once per session.
4: The listener starts the external procedure agent and returns its address to the database.
5: The database transmits all necessary information to call the function to the external procedure agent. The agent converts PL/SQL data types.
6: The external procedure agent loads the shared library (dll) and calls the function.


If either step 3 or 4 fails, an ORA-28575 is returned.