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 separate from the default extproc task. As with the default arrangement, if more than one user invokes the external procedure, Oracle will still spawn one task per user; there is no way to get "pooling" of extproc processes.
Oracle also supports a more flexible arrangement that allows you to pass in the name of the agent as a parameter to the external procedure. To take advantage of this feature, use the AGENT IN clause in the call spec. For example (changes are printed in bold):
CREATE OR REPLACE PROCEDURE shell2 (name_of_agent IN VARCHAR2, cmd VARCHAR2)AS LANGUAGE C LIBRARY extprocshell_lib NAME "extprocsh 2 " AGENT IN (name_of_agent) WITH CONTEXT PARAMETERS (CONTEXT, name_of_agent STRING, cmd STRING, cmd INDICATOR);Notice that I had to include the name of the agent in the list of parameters. Oracle enforces a rule that every formal parameter must have a corresponding entry in the PARAMETERS clause. So I have to modify my external C library. In my case, I merely added a second entry point, extprocsh2(), to the library with the following trivial function:
void extprocsh2(OCIExtProcContext *ctx, char *agent, char *cmd, short cmdInd){ extprocsh(ctx, cmd, cmdInd);}My code just ignores the agent string. Now, though, I can invoke my shell2 procedure as in the following:
CALL shell2('agent1', ' whatever ');INSERT p. 949If you want your stored program to somehow invoke an external procedure on a remote machine, you have at least three potential options. First, you could implement an external procedure on the local machine which is just a "pass-through" program, making a C-based remote procedure call on behalf of PL/SQL. Second, you could implement a stored PL/SQL program on the remote machine as an external procedure, and call it from the local machine via a database link. The third option—connecting directly to a remote agent from the local stored PL/SQL—does not seem to be officially supported by Oracle. While it is possible to set up an external procedure listener to accept connections over the network (TCP as opposed to IPC), all of my attempts to connect to a remote agent have failed. If I ever do get it working, though, I'll post a how-to on the O'Reilly web site.
| A Debugging Odyssey To help debug external procedures, Oracle supplies a script, dbgextp.sql, which you should be able to find it in the plsql/demo directory. The script builds a package named DEBUG_EXTPROC and an associatedlibrary named debug_extproc_library. Using this package, I was able to demonstrate that the GNU debugger (gdb 4.18) can attach to a running process and debug external procedures. Here's how I got this to work, first on Solaris, then onWindows XP. As preliminary steps, I compiled the shared library file with the compiler option (-g) needed to include symbolic information for the debugger. I also ran the dbgextp.sql script. In a fresh SQL*Plus session, I then ran: SQL> EXEC DEBUG_EXTPROC.startup_extproc_agent This caused an extproc process to launch, whose process id (PID) I discovered using ps -ef. At this point I started the debugger and tried to attach to the running process. gdb $ORACLE_HOME/bin/extproc pid However, I got a "permission denied" error. Because I already had permission to read and execute the extproc program file, I wound up logging in as the oracle account to get past the error. (There is probably a better way.) I then set a breakpoint on the "pextproc" symbol per the instructions in the dbgextp.sql file. Next, in my SQL*Plus session, I invoked the procedure using: SQL> CALL shell(NULL); When the external procedure was called, extproc hit the breakpoint. After executing a gdb "share" command so the debugger would read the symbols in my just-loaded external shared library, I was able to set a breakpoint on the extprocsh() external procedure. It worked pretty well after that. While debugging on Microsoft platforms is also possible, I wouldn't exactly call what I did "graceful." These are the major changes in the debugging procedure: 1. I modified the external procedure listener service in the Windows control panel to execute under the authority of my own user account rather than under that of "Local System." 2. Because the debugger couldn't find pextproc and because the gdb "share" command doesn't seem to apply to MS Windows, I had to add the line of code "DebugBreak();" at the desired breakpoint in the C program and recompile. I also needed to "#include <windows.h>". 3. Instead of ps -ef, I used Microsoft's tasklist.exe program to obtain the extproc PID. My Unix tests were on Solaris 2.6; my Microsoft tests were on Windows XP using the Cygwin gdb 5.0, which has both a console version and a GUI version. |
