Execute operating systems commands from PL/SQL (external procedure call)

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

Tags:

Syed