/*-------------------------------------------------------------------- * 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); }