Subject:  Calling Operating System Commands from PL/SQL using External Procedures 
Creation Date:  15-FEB-2000 
 

Overview
--------

The ability to call operating system commands from PL/SQL is a feature that
is easily implemented in Oracle8 using External Procedures.  This article
contains a simple example of implementing this functionality.

Note:  The example in this article was created on Solaris and tested with 
       Oracle 8.0.4 and 8.0.5.


Listener Configuration
----------------------

The following represents a typical listener.ora configuration.  Details
for your particular installation may vary.

LISTENER_PROC =
  (ADDRESS_LIST =
        (ADDRESS= (PROTOCOL= IPC)(KEY=external))
        (ADDRESS= (PROTOCOL= TCP)(HOST=otcsol1)(PORT=23000))
  )

SID_LIST_LISTENER_PROC =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = L804)
      (ORACLE_HOME = /u11/app/oracle/product/8.0.4)
    )
    (SID_DESC =
      (SID_NAME = external)
      (ORACLE_HOME = /u11/app/oracle/product/8.0.4)
      (PROGRAM = /u11/app/oracle/product/8.0.4/bin/extproc)
      (ENVS='PATH=/bin:/usr/bin:/usr/ccs/bin:/usr/ucb')
    )
  )

# The ENVS is used to define any environment variables that will
# be used by the external procedure.

STARTUP_WAIT_TIME_LISTENER_PROC = 0
CONNECT_TIMEOUT_LISTENER_PROC = 10
TRACE_LEVEL_LISTENER_PROC = OFF  


TNSNAMES.ORA Configuration
--------------------------

The following represents a typical tnsnames.ora configuration.  Details
for your particular installation may vary:

extproc_connection_data =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = external))
    (CONNECT_DATA = (SID = external)(SERVER=DEDICATED))
  )

L804=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(Host = otcsol1)(Port = 23000))
    (CONNECT_DATA = (SID = L804))
  )             


External Procedure Source Code
------------------------------

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

void sh(char *);

void sh( char  *cmd )
{
        int num;

        num = system(cmd);
}

Issue the following commands to compile the code and generate the shared 
object in Solaris:

cc -G -c shell.c
ld -r -o shell.so shell.o

You can also use the demo_rdbms.mk makefile to build shared libraries
for use in external procedures.  Using this method insulates you from
any Operating System specific dependencies (e.g., which flags to use 
for ld).

For example:

    In 8.0.X,

      $ make -f demo_rdbms.mk extproc_nocallback \
          SHARED_LIBNAME=shell.so OBJS=shell.o

    In 8.1.X,

      $ make -f demo_rdbms.mk extproc_no_context \
          SHARED_LIBNAME=shell.so OBJS=shell.o


Library Definition
------------------

CREATE LIBRARY shell_lib is '/u11/home/lsupport/proc/shell.so';
/

Note:  The directory in quotes is the current of location of the library that
       was created in the steps above.


PL/SQL Wrapper Procedure
------------------------

create or replace procedure shell(cmd IN char)
as external
   name "sh"
   library shell_lib
   language C
   parameters (cmd string);
/                                  


Execution
---------

SQL> exec shell('ls');

cli.trc           exe_prba.sql      prueba.mk         shell.c.old
core              listener.old      prueba.o          shell.o
dec2bin.c         listener.ora      prueba.so         shell.so
dec2bin.c.old     nena.lst          prueba.sql        shell.sql
dec2bin.o         p.sql             sal.1             shell.sql.old
dec2bin.so        pepito.lst        sal.2             sqlnet.log
dec2bin.sql       prb               sal.3             tnsnames.ora
dec2bin.sql.old   prb.c             salida.lst        tnsnames.ora.old
envoltorio.sql    prueba.c          shell.c           uno.sql

PL/SQL procedure successfully completed.  

The output produced by the executed command is not viewable in general since 
it is directed to the controlling terminal for the extproc process.  The
extproc process inherits its controlling terminal from the listener, which in
turn inherits its terminal from the shell used to start the listener.  If this
shell is no longer visible, the output is never displayed.

To see the output returned from the system command, redirect the output to a 
file and then view/process the output file.  This can be done simply on UNIX
platforms by appending "> myoutput.txt" to the command being executed. 
Standard error can be redirected similarly.

The executed commands only see the directory pointed by the TNS_ADMIN
environment variable defined in the server, therefore, when specifying a file,
be sure to include the path of its desired location.


References
----------

[NOTE:14082.1]  Dynamic SQL and System Commands Using DBMS_PIPE

[NOTE:74159.1]  External Procedures Using Pro*C

@ [NOTE:76411.1]  PL/SQL External Procedures in Oracle8

"PL/SQL User's Guide and Reference, Release 8.0"

"Oracle8i Application Developer's Guide - Fundamentals"