在PL/SQL中執行作業系統的命令

There is no direct way to execute operating system commands from PL/SQL. PL/SQL doesn't have a "HOST" command, like in SQL*Plus, that allows users to call OS commands. Nevertheless, the following workarounds can be used:

Database Pipes
Write an external program (using one of the precompiler languages, OCI or Perl with Oracle access modules) to act as a listener on a database pipe (SYS.DBMS_PIPE). Your PL/SQL program then put requests to run commands in the pipe, the listener picks it up and run the requests. Results are passed back on a different database pipe. For an Pro*C example, see chapter 8 of the Oracle Application Developers Guide.

External Procedure Listeners:
From Oracle8 one can call external 3GL code in a dynamically linked library (DLL or shared object). One just write a library in C/ C++ to do whatever is required. Defining this C/C++ function to PL/SQL makes it executable. 詳見全文

Using Java
More detail later...

DBMS_SCHEDULER
In Oracle 10g and above, one can execute OS commands via the DBMS_SCHEDULER package. Look at this example:

BEGIN
  dbms_scheduler.create_job(job_name        => 'myjob',
                            job_type        => 'executable',
                            job_action      => '/app/oracle/x.sh',
                            enabled         => TRUE,
                            auto_drop       => TRUE);
END;
/

exec dbms_scheduler.run_job('myjob');

/*--------------------------------------------------------------------
 * extproc.c  
 * 
 * Call operating system commands from PL/SQL using the External 
 * Procedure Interface.
 *
 *                                              Frank Naude - Dec 2000
 *-------------------------------------------------------------------- 
 * Setup instructions:
 *
 * 1. Compile this program: cc -G extproc.c -o extproc.so (on Unix)
 * 2. Run $ORACLE_HOME/bin/extproc to ensure it is executable
 * 3. Define this TNSNAMES.ORA entry (Use the correct domain):
 *       EXTPROC_CONNECTION_DATA.WORLD = (DESCRIPTION =
 *                          (ADDRESS=(PROTOCOL=IPC)(KEY=extproc))
 *                          (CONNECT_DATA=(SID=extproc)))
 * 4. Define this LISTENER.ORA entry:
 *       EXTERNAL_PROCEDURE_LISTENER =
 *          (ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=extproc)))
 *       SID_LIST_EXTERNAL_PROCEDURE_LISTENER =
 *          (SID_LIST=(SID_DESC=(SID_NAME=extproc)
 *              (ORACLE_HOME=/app/oracle/product.8.1.7)(PROGRAM=extproc)))
 * 5. Start the new listener: lsnrctl start EXTERNAL_PROCEDURE_LISTENER
 * 6. SQL> create library shell_lib as '/app/oracle/local/extproc.so'; 
 *         /
 * 7. SQL> create or replace function sysrun (syscomm in varchar2)
 *         return binary_integer
 *         as language C  -- Use "as external" for older Oracle releases
 *            name "sysrun"
 *            library shell_lib
 *            parameters(syscomm string);
 *         /
 * 8. Execute an OS command from PL/SQL: 
 *       PL/SQL> declare
 *                  rc number;
 *               begin
 *                  rc := sysrun('/bin/ls -l');
 *                  dbms_output.put_line('Return Code='||rc);
 *               end;
 *               /
 *
 *-------------------------------------------------------------------- 
 * Notes: 
 *
 * 1. When running shell-scripts, very few environment variables will be
 *    defined (as with cron jobs). Remember to set everything 
 *    explicitly. Ie. $PATH, etc.
 * 2. Rewrite this program using C Piping if you need to capture command 
 *    output. Look at the popen (pipe open) function.
 * 3. In addition to this, you can also try to make the external 
 *    procedure example as provided by Oracle:
 *       $ cd $ORACLE_HOME/plsql/demo
 *       $ make -f demo_plsql.mk extproc.so
 *
 *-------------------------------------------------------------------- 
 */

int sysrun(char *command)
{
   return system(command);
}

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7607759/viewspace-22220/，如需轉載，請註明出處，否則將追究法律責任。