DOYENSYS Knowledge Portal

We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal

Tuesday, January 21, 2014

Calling Operating System Commands From PL/SQL Using External Procedures

Calling Operating System Commands From PL/SQL Using External Procedures (Doc ID 99136.1)

The ability to call operating system commands from PL/SQL is a feature that
is easily implemented in Oracle8 using External Procedures.  This article
contains a simple example of implementing this functionality.


This example code was last tested with Oracle Database or later but is no longer necessary since the inclusion of OS Commands / Executables in the DBMS_SCHEDULER API.
Also, the standard disclaimer of supportability applies to this note. As this functionality is not provided by the Oracle Database Development group, this code is provided for demonstration purposes only and has not been tested. Be sure and test before placing in production, and in this case, confirm any issue demonstrates via the DBMS_SCHEDULER API before contacting Oracle Support.


Listener Configuration
The following represents a typical listener.ora configuration.  Details for your particular installation may vary.
Starting with 11g, there is no further need to configure the Listener, but the capability still exists.
11g provides an extproc.ora file located in the $ORACLE_HOME/hs/admin directory.
To use the extproc.ora file and bypass the listener do the following:
1.  comment out or remove any entry of "extproc_connection_data" from the tnsnames.ora on the server.
2. In file $ORACLE_HOME/hs/admin/extproc.ora add the line (or modify the existing value)
        (ADDRESS= (PROTOCOL= IPC)(KEY=external))
        (ADDRESS= (PROTOCOL= TCP)(HOST=otcsol1)(PORT=23000))
    (SID_DESC =
      (SID_NAME = EMR102U6)
      (ORACLE_HOME = /emea/rdbms/64bit/app/oracle/product/10.2.0)
    (SID_DESC =
      (SID_NAME = external)
      (ORACLE_HOME = /emea/rdbms/64bit/app/oracle/product/10.2.0)
      (PROGRAM = /emea/rdbms/64bit/app/oracle/product/10.2.0/bin/extproc)
# The ENVS is used to define any environment variables that will be used by the external procedure.
# The EXTPROC_DLLS=ANY allows any shared library to be referenced and could be used with a specific directory reference.
TNSNAMES.ORA Configuration
The following represents a typical tnsnames.ora configuration.  Details for your particular installation may vary:
extproc_connection_data =
    (ADDRESS = (PROTOCOL = IPC)(KEY = external))
    (ADDRESS = (PROTOCOL = TCP)(Host = otcsol1)(Port = 23000))
    (CONNECT_DATA = (SID = EMR102U6))


This sample code is provided for educational purposes only, and is not supported by Oracle Support. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.


External Procedure Source Code
void sh(char *);
void sh( char  *cmd )
        int num;
        num = system(cmd);
If not referencing any Oracle specific commands like OCI, building the shared library can be done using one of two
Option 1 
Manually issue the following commands to compile the code and generate the shared object in Solaris:
cc -G -c shell.c
ld -r -o shell.o
Option 2
Use the makefile which is located under the $ORACLE_HOME/rdbms/demo directory 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:
    Using version 8.1.x and higher:
      $ make -f extproc_no_context \
Library Definition
It is assumed the shared library is located in the '/u11/home/proc' directory.
CREATE LIBRARY shell_lib is '/u11/home/proc/';

The directory in quotes is the current of location of the library that  was created in the steps above.
PL/SQL Wrapper Procedure

create or replace procedure shell(cmd IN char)
as external
   name "sh"
   library shell_lib
   language C
   parameters (cmd string);


Execution - Sample 1
SQL> exec shell('ls');
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. See Sample 2 below which does an 'ls' within the /tmp directory and 
redirects the output to the output.txt file.

Execution - Sample 2
exec shell('ls /tmp > /tmp/output.txt');
SQL> !ls -l output.txt
-rw-r--r--   1 emrdbms  emrdbms       87 Oct 26 09:02 output.txt
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.
If the shared object does not have sufficient privileges for the calling user, specifically execute, you may see an
error that includes :
ORA-06520: PL/SQL: Error loading external library
ORA-06522: Cannot map text for library: mmap(0x0, 0x6b0, 0x5, 0x41, 6, 0x0) returns Permission denied.
ORA-06512: at "TESTUSER.SHELL", line 1
The resolution is to ensure that the operating system user that own the oracle executable (typically oracle) has execute
privilege on the shared object library.