Oracle PL/SQL Programming, 5th Edition

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 file (dynamically linked library) on Microsoft operating systems; on Solaris, AIX, and Linux, you’ll usually see shared libraries with a .so (shared object) file extension, or .sl (shared library) on HP-UX. 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.

Example: Invoking an Operating System Command

Our first example allows a PL/SQL program to execute any operating system-level command. Eh? I hope your mental security buzzer is going off—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 still object to granting wide permissions to run this code. Just try to suspend your disbelief as we walk through the examples.

The first example consists of a very simple C function, extprocsh( ), which accepts a string and passes it to the system function for execution:

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

The function returns ...