How to run an OS command from within Oracle

This is something that comes up occasionally, and I always end up searching Google to find the answer, so I documented it the last time so I had the full instructions. Here they are.

The working directory is important:

cd /u01/app/oracle/dba/work/andy

Create the program in this directory, vi a file and call it shell.c :

#include

#include

#include

void sh(char *command) {

int num;

num = system(command);

}

Compile the program:

/usr/local/bin/gcc -G -c shell.c

ld -r -o shell.so shell.o

chmod 775 shell.so

Create the directory and the library in the database:

SQL> create directory shell_lib as '/u01/app/oracle/dba/work/andy';

SQL> create library shell_lib as '/u01/app/oracle/dba/work/andy/shell.so';

SQL> /

Create the procedure:

SQL> CREATE OR REPLACE PROCEDURE shell(command IN char)

> AS EXTERNAL

> NAME "sh"

> LIBRARY shell_lib

> LANGUAGE C

> PARAMETERS (command string);

> /

Set up the listener.ora and tnsnames.ora:

listener.ora:

TSTLSNR =

(ADDRESS_LIST =

(ADDRESS= (PROTOCOL= IPC)(KEY=EXTPROC))

(ADDRESS= (PROTOCOL= TCP)(HOST=10.208.10.1)(PORT=1525))

)

SID_LIST_TSTLSNR =

(SID_LIST =

(SID_DESC =

(SID_NAME = andy)

(ORACLE_HOME = /u01/app/oracle/product/8.1.7)

)

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /u01/app/oracle/product/8.1.7)

(PROGRAM = extproc)

)

)

tnsnames.ora:

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

(CONNECT_DATA = (SID = PLSExtProc)(SERVER=DEDICATED))

)

andy =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(Host = 10.208.10.1)(Port = 1525))

(CONNECT_DATA = (SID = andy))

)

Run the procedure:

SQL> exec shell('ls');

andyalterTablespace.sh cr_db.sh logs new_temp.sql shell.c

andyrun.sh cr_user.sql mga_performance.dmp performance shell.o

andyrun1.sh data mga_performance.log replace_temp.log shell.so

andyrun2.sh listener.andy1 new scripts

PL/SQL procedure successfully completed.

So this should work, just make sure all the elements are present. One thing to double-check is the names - a typo or the wrong directory or script name will cause this not to work and you could spend ages trying to figure out where it's gone wrong.