EXTPROC
Scope and Application
A list of the essential components for executing an external procedure is included below, along with a brief description of the role of each.
Database:
Responsible for storing any information required to locate and execute an external procedure (e.g. the location of shared library, number and types of arguments, etc.)
Initiates the call to the procedure via the listener and external procedure agent
Both the library alias and call specification are stored in the database.
Llistener:
Listens for external procedure requests from databases and starts external procedure agent processes (one per session)
External procedure agent:
Accepts information (arguments, shared library location, etc.) from the database, loads the shared library as required, and executes the corresponding function in some other language
Corresponds to the extproc executable which resides in $ORACLE_HOME/bin
User-provided shared library:
Contains the user-written function that implements the desired functionality
Library alias:
Records the location of the shared library within the database
Used in the PL/SQL call specification
External procedure call specification:
Provides a description of the procedure, including its PL/SQL name, arguments and types, and the name of the library alias and the function (written in another language) implementing the procedure, or libunit in the case of a Java class method
External Procedures – Troubleshooting ORA-28575 Errors
A Typical Sequence of Events
The following is a typical sequence of events:
The user invokes the call specification corresponding to an external procedure via PL/SQL or some other programmatic interface.
The database examines the call specification to find the name of the shared library implementing the function, as well as the name of the function and the number and type of the arguments.
The database uses the extproc_connection_data service name in the tnsnames.ora file to contact the external procedure listener.
Note: This step only occurs the first time the external procedure is invoked within a given session. After that, the existing external procedure agent can be contacted directly
The listener starts an external procedure agent process to service the external procedure execution request and returns the address of the new extproc agent to the requesting database.
The database sends the external procedure agent the name of the shared library, the name of the function to invoke, and after performing the required conversions from PL/SQL to another language datatypes, the actual arguments.
The external procedure agent loads the shared library, invokes the appropriate function, converts any returned data from another language datatypes to the corresponding PL/SQL types, and sends the data back to the database.