Oracle DBA Tips Corner      Return to the Oracle DBA Tips Corner.

Calling OS Commands from PL/SQL using External Procedures - (extproc)

by Jeff Hunter, Sr. Database Administrator

Oracle provides the ability to call operating system commands from PL/SQL with a new feature called external procedures. The external procedures feature was added in Oracle8. This month I will give an overview of the steps involed in setting up external procedures. These examples where created on Solaris and tested with 8.0.4, 8.0.5 and 8.1.6.

Changes in Oracle9i

If you are using Oracle9i, please read my article entitled "Changes in Configuring External Procedures in Oracle 9.2.0". It includes several security changes that have to be configured to enable PL/SQL External Procedures.

listener.ora

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

listener.ora Configuration

LISTENER = (ADDRESS_LIST = (ADDRESS= (PROTOCOL= IPC)(KEY=EXTPROC)) (ADDRESS= (PROTOCOL= TCP)(HOST=testdb)(PORT=1521)) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = ORA816) (ORACLE_HOME = /u01/app/oracle/product/8.1.6) ) (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/8.1.6) (PROGRAM = extproc) ) ) STARTUP_WAIT_TIME_LISTENER_PROC = 0 CONNECT_TIMEOUT_LISTENER_PROC = 10 TRACE_LEVEL_LISTENER_PROC = OFF

Listing 1 - Sample listener.ora file

tnsnames.ora Configuration

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

tnsnames.ora

EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) (CONNECT_DATA = (SID = PLSExtProc)(SERVER=DEDICATED)) ) ORA816= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(Host = testdb)(Port = 1521)) (CONNECT_DATA = (SID = ORA816)) )

Listing 2 - Sample tnsnames.ora file

External procedure source code

shell.c

#include<stdio.h> #include<stdlib.h> #include<string.h> void mailx(char *to, char *subject, char *message) { int num; char command[50000]; strcpy(command, "echo \""); strcat(command, message); strcat(command, "\" | mailx -s \""); strcat(command, subject); strcat(command, "\" "); strcat(command, to); num = system(command); } void sh(char *command) { int num; num = system(command); }

Listing 3 - Sample external procedure code

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

  gcc -G -c shell.c
  ld -r -o shell.so shell.o
  chmod 775 shell.so

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 '/u01/app/oracle/c/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 PL/SQL Wrapper Procedures

CREATE OR REPLACE PROCEDURE shell(command IN char) AS EXTERNAL NAME "sh" LIBRARY shell_lib LANGUAGE C PARAMETERS (command string); / CREATE OR REPLACE PROCEDURE mailx(send_to IN char, subject IN char, message IN char) AS EXTERNAL NAME "mailx" LIBRARY shell_lib LANGUAGE C PARAMETERS (send_to string, subject string, message string); /

Listing 4 - Sample PL/SQL Wrapper Procedure

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.


  SQL> exec mailx('Jeffrey.Hunter@marconi.com', 'EXTPROC Test', 'This is a test');

  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.