Managing Processes for External Procedures

About External Procedures

External procedures are procedures that are written in C, C++, Java, or other language, compiled and stored outside the database, and then called by user sessions. For example, a PL/SQL program unit could 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 in other languages.

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

DBA Tasks to Enable External Procedure Calls

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 found in the subsection entitled "Set Up the Environment" in the chapter "Developing Applications with Multiple Programming Languages" in Oracle Database Advanced Application Developer's Guide.

• Managing libraries or granting privileges related to managing libraries

  The database requires DLLs 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:

  • 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, and EXECUTE ON library_name.

  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.