5.7 Managing Processes for External Procedures

«« Previous
Next »»

An external procedure is a procedure or function written in a programming language and stored in a shared library. An Oracle server can call external procedures or functions using PL/SQL routines.

5.7.1 About External Procedures

External procedures are procedures that are written in a programming language such as C, C++, or Java, compiled, and stored outside of the database, and then called by user sessions. For example, a PL/SQL program unit can call one or more C routines that are required to perform special-purpose processing.

These callable routines are stored in a dynamic link library (DLL), or a libunit in the case of a Java class method, and are registered with the base language. Oracle Database provides a special-purpose interface, the call specification (call spec), that enables users to call external procedures.

When a user session calls an external procedure, the database starts an external procedure agent on the database host computer. The default name of the agent is extproc. Each session has its own dedicated agent. Optionally, you can create a credential so that the agent runs as a particular operating system user. When a session terminates, the database terminates its agent.

User applications pass to the external procedure agent the name of the DLL or libunit, the name of the external procedure, and any relevant parameters. The external procedure agent then loads the DLL or libunit, runs the external procedure, and passes back to the application any values returned by the external procedure.

5.7.2 DBA Tasks to Enable External Procedure Calls

To enable external procedure calls, you must modify the listener and manage libraries.

Enabling external procedure calls may involve the following DBA tasks:
  • Configuring the listener to start the extproc agent
By default, the database starts the extproc process. Under the following circumstances, you must change this default configuration so that the listener starts the extproc process:
  • You want to use a multithreaded extproc agent
  • The database is running in shared server mode on Windows
  • An AGENT clause in the LIBRARY specification or an AGENT IN clause in the PROCEDURE or FUNCTION specification redirects external procedures to a different extproc agent
Instructions for changing the default configuration are in Oracle Database Development Guide.
  • Managing libraries or granting privileges related to managing libraries
The database requires DLL statements to be accessed through a schema object called a library. For security purposes, by default, only users with the DBA role can create and manage libraries. Therefore, you may be asked to:
  • Create a directory object using the CREATE DIRECTORY statement for the location of the library. After the directory object is created, a CREATE LIBRARY statement can specify the directory object for the location of the library.
  • Create a credential using the DBMS_CREDENTIAL.CREATE_CREDENTIAL PL/SQL procedure. After the credential is created, a CREATE LIBRARY statement can associate the credential with a library to run the extproc agent as a particular operating system user.
  • Use the CREATE LIBRARY statement to create the library objects that the developers need.
  • Grant the following privileges to developers: CREATE LIBRARY, CREATE ANY LIBRARY, ALTER ANY LIBRARY, EXECUTE ANY LIBRARY, EXECUTE ON library_name, and EXECUTE ON directory_object.
Only make an explicit grant of these privileges to trusted users, and never to the PUBLIC role. If you plan to create PL/SQL interfaces to libraries, then only grant the EXECUTE privilege to the PL/SQL interface. Do not grant EXECUTE on the underlying library. You must have the EXECUTE object privilege on the library to create the PL/SQL interface. However, users have this privilege automatically in their own schemas. Explicit grants of EXECUTE object privilege on a library are rarely required.

«« Previous
Next »»