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.
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.
