Calling an operating system command from within PLSQL

Question:
For years, the only way to execute an operating system command was to use DBMS_PIPE to "pipe" out the command to an operating system shell script. Is there now an easier way to do this in PL/SQL?
Answer:
Oh, yes, executing OS commands has gotten much easier in PL/SQL, with the introduction (back in Oracle8 Database) of external procedures! Having said that, it's still not exactly a piece of cake, and this answer cannot address all the issues that are likely to come. Instead, I offer below an extract (written by my co-author, Bill Pribyl) from the fourth edition of Oracle PL/SQL Programming, Fourth Edition (O'Reilly), Chapter 27 ("External Procedures") to give you an idea of what is involved.
(Start Excerpt)
This example consists of a very simple C function, extprocsh(), which accepts a string and passes it to the system function for execution:
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 source code in a file named extprocsh.c, I can use the GNU C compiler to generate a shared library. On my 64-bit Solaris machine running GCC 3.4.2 and Oracle Database 10g Release 2, I used the following compiler command:
gcc -m64 extprocsh.c -fPIC -G -o extprocsh.so
Similarly, on Microsoft Windows XP Pro running GCC 3.2.3 from Minimal GNU for Windows (MinGW), also with Oracle Database 10g Release 2, this works:
c:\MinGW\bin\gcc extprocsh.c -shared -o extprocsh.dll
These commands generate a shared library file, extprocsh.so or extprocsh.dll. Now I need to put the library file somewhere that Oracle can find it. Depending on your Oracle version, that may be easier said than done!

After copying the file and/or making adjustments to the listener, I also need to define a "library" inside Oracle to point to the DLL:

CREATE OR REPLACE LIBRARY extprocshell_lib
AS '/u01/app/oracle/local/lib/extprocsh.so'; -- Unix

CREATE OR REPLACE LIBRARY extprocshell_lib
AS 'c:\oracle\local\lib\extprocsh.dll'; -- Microsoft
Don't by confused by the term "library" here; it's really just a file name alias that can be used in Oracle's namespace. Also note that performing this step requires Oracle's CREATE LIBRARY privilege, which is one of the security hoops I mentioned earlier.
Now I can create a PL/SQL call specification that uses the newly created library:
CREATE OR REPLACE FUNCTION shell(cmd IN VARCHAR2)
RETURN PLS_INTEGER
AS
LANGUAGE C
LIBRARY extprocshell_lib
NAME "extprocsh"
PARAMETERS (cmd STRING, RETURN INT);
That's all there is to it! Assuming that the DBA has set up the system environment to support external procedures, shell() is now usable anywhere 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.
For example:
DECLARE
result PLS_INTEGER;
BEGIN
result := shell('cmd'));
END;
Or even:

SQL> SELECT shell('cmd') FROM DUAL;
If successful, this will return zero:
SHELL2('cmd')
-------------
0
Keep in mind that if the operating system command would normally display output to stdout or stderr, that output will go to the bit bucket unless you modify your program to return it to PL/SQL. You can, subject to OS-level permissions, 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')); -- Unix
result := 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.
(End Excerpt)
That should give you an idea of what you need to do. For more information about external procedures, check out the Application Developer's Guide - Fundamentals book.
In addition to relying on external procedures, if you are running Oracle Database 10g, then you can also take advantage of DBMS_SCHEDULER. According to chapter 93 of the Oracle Database PL/SQL Packages and Types Reference, when you submit a job, you can specify "that the job is a job external to the database. External jobs are anything that can be executed from the operating system's command line. Anydata arguments are not supported with a job or program type of EXECUTABLE. The job owner must have the CREATE EXTERNAL JOB system privilege before the job can be enabled or run."