Chapter 23. External Procedures

Backin the Oracle7 days, it was common to hear the question "Can I call whatever from within Oracle?" Typically, whatever had something to do with sending email, running operating system commands, or using some non-PL/SQL language feature. Although email has pretty much been a non-issue since Oracle began shipping the built-in UTL_SMTP package, there are by now quite a handful of alternatives to calling "whatever." Here are the most common approaches:

· Write the program as a Java stored procedure and call the Java from PL/SQL

· Use a database table or queue as a place to store the requests, and create a separate process to read and respond to those requests

· Use a database pipe and write a daemon that responds to requests on the pipe

· Write the program in C and call it as an external procedure

Let's look at each of these approaches in turn. Java may work well, if it's fast enough for your application. Queueing is a very interesting technology, but even if you are simply using plain tables, this approach requires two Oracle sessions: one to write to the queue and one to read from it. Moreover, two sessions means two different transaction spaces, and that might be a problem for your application. Database pipe-based approaches also have the two-session problem, not to mention the challenge of packing and unpacking the contents of the pipe. In addition, handling many simultaneous requests using any of these approaches might require you to create your own listener and process-dispatching system.

Those are all reasons to consider the fourth option. External procedures allow PL/SQL to do almost anything that any other language can do, and can remedy the shortcomings of the other approaches just mentioned. PL/SQL programmers typically have many questions about using external procedures. How do external procedures work? Are they secure? How can I build my own? What are their advantages and disadvantages? This chapter addresses these questions and provides examples of commonly used features of external procedures.

23.1 Introduction to External Procedures

To call an external program from inside Oracle, the program must run as a shared library. You probably know this type of program as a DLL (dynamically linked library) on Microsoft operating systems; on Unix, you'll usually see shared libraries with a .so (shared object) file extension. In theory, you can write the external routine in any language you wish, but your compiler and linker will need to generate the appropriate shared library format that is callable from C. You "publish" the external program by writing a special PL/SQL wrapper, known as a call specification. If the external function returns a value, it maps to a PL/SQL function; if the external function returns nothing, it maps to a PL/SQL procedure.

23.1.1 Example: Invoking an Operating System Command

Our first example allows a PL/SQL program to execute any system-level command. Huh? I hope your mental "OracleHomeLand" security buzzer is going off right about now—that sounds like a really dangerous thing to do, doesn't it? Despite several security hoops you have to jump through to make it work, your database administrator will probably still want to intervene to really tighten up the ship. For now, try to suspend your disbelief, and start paddling.

This example uses a very simple C function, extprocsh( ), which accepts a string and asks the system function to execute it as a command:

int extprocsh(char *cmd){ return system(cmd);}

The function returns the result code as provided by system, a function normally found in the C runtime library (libc) on Unix, or in msvcrt.dll on Microsoft platforms.

After saving the function in a file named extprocsh.c, I can use the GNU C compiler to generate a shared library:

gcc -c extprocsh.cgcc -shared -o extprocsh.so extprocsh.o -- Naming convention for most Unixesgcc -shared -o extprocsh.dll extprocsh.o -- Microsoft Windows naming convention

These commands generate an object file, extprocsh.o, and a shared library file, extprocsh.so or extprocsh.dll. Now I need to put the library file somewhere that Oracle can find it.^[1]

^[1] A security enhancement of Oracle Net in Oracle9i Release 2 restricts (by default) the allowed directory to those indicated. Unfortunately, putting custom files in the Oracle distribution directories in this way is a bad idea from an administrator's point of view.

Assuming that an ORACLE_HOME environment variable is set, I simply copy the file from wherever I compiled it:

cp extprocsh.so $ORACLE_HOME/lib/ -- Unixcopy extprocsh.dll %ORACLE_HOME%\bin\ -- Microsoft

I also need to define a "library" inside Oracle to point to the DLL. If the DBA has granted me the CREATE LIBRARY privilege, I can execute SQL commands such as those shown here:

CREATE OR REPLACE LIBRARY extprocshell_lib AS '/u01/app/oracle/9.2/lib/extprocsh.so'; -- Unix CREATE OR REPLACE LIBRARY extprocshell_lib AS 'c:\oracle\ora92\bin\extprocsh.dll'; -- Microsoft

Now I have a library named extprocshell_lib. The term library is something of a misnomer because it's really a thin, sort of "see-through" object. It merely provides an alias for the operating system path that can live in the Oracle namespace.

I am now ready to create a PL/SQL call specification, as shown here:

CREATE OR REPLACE FUNCTION shell(cmd IN VARCHAR2) RETURN PLS_INTEGERAS LANGUAGE C LIBRARY extprocshell_lib NAME "extprocsh" PARAMETERS (cmd STRING, RETURN INT);

This call spec maps the C parameters to PL/SQL parameters, making shell( ) callable from anywhere that you can invoke a PL/SQL function (SQL*Plus, Perl, Pro*C, etc.). From an application programming perspective, calling an external procedure is indistinguishable from calling a conventional procedure.

Assuming that the DBA has set up the system environment to support external procedures (see Section 23.2.1 later in this chapter), it's easy to invoke the program. You can specify:

DECLARE result PLS_INTEGER;BEGIN result := shell('operating system command'));END;

Or perhaps:

SQL> VAR res NUMBERSQL> CALL shell('operating system command') INTO :res;

Keep in mind that if the operating system command would normally display output to stdout or stderr, you can instead (if privileges permit) redirect that output to a file. Here is a trivial example of saving a file containing a directory listing:

result := shell('ls / > /tmp/extproc.out')); -- Unixresult := shell('cmd /c "dir c:\ > c:\temp\extproc.out"')); -- Microsoft

These operating system commands will execute with the same privileges as the Oracle Net listener that spawns the extproc process. Hmmm, I bet your DBA or security guy will want to change that. Read on if you want to help.