Oracle PL/SQL Programming, Third Edition

Nondefault Agents

As of Oracle9i, it became possible to run external procedure agents via database links that connect to other 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:

agent1 =
  (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 agent1link 
  2  CONNECT TO 
               username
                IDENTIFIED BY 
               password
  3  USING 'agent1';

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 'agent1';

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