Oracle PL/SQL Programming, 5th Edition

Nondefault Agents

Starting with Oracle9i Database, it is possible to run external procedure agents via database links that connect to other local database servers. This functionality enables you to spread the load of running expensive external programs onto other database instances.

Even without other servers, running an external procedure through a nondefault agent launches a separate process. This can be handy if you have a recalcitrant external program. Launching it via a nondefault agent means that even if its extproc process crashes, it won’t have any effect on other external procedures running in the session.

As a simple example of a nondefault agent, here is a configuration that allows an agent to run on the same database but in a separate extproc task. The tnsnames.ora file needs an additional entry such as:

agent0 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY=extprocKey))
    (CONNECT_DATA = (SID = PLSExtProc))
  )

Here, extprocKey can just be the same key as in your EXTPROC_CONNECTION_DATA entry.

Because agents are created with a database link, we’ll need to create one of those:

SQL> CREATE DATABASE LINK agent0link
  2  CONNECT TO username IDENTIFIED BY password
  3  USING 'agent0';

Now, finally, the agent can appear in a CREATE LIBRARY statement such as:

CREATE OR REPLACE LIBRARY extprocshell_lib_with_agent
   AS 'c:\oracle\admin\local\lib\extprocsh.dll'
   AGENT 'agent0';

Any call spec that was written to use this library will authenticate and connect through this agent0 link, launching an ...