Tuesday, January 21, 2014
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.
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.
The following represents a typical listener.ora configuration. Details for your particular installation may vary.
The following represents a typical tnsnames.ora configuration. Details for your particular installation may vary:
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
If not referencing any Oracle specific commands like OCI, building the shared library can be done using one of two
Manually issue the following commands to compile the code and generate the shared object in Solaris:
Use the demo_rdbms.mk 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).
Using version 8.1.x and higher:
It is assumed the shared library is located in the '/u11/home/proc' directory.
PL/SQL Wrapper Procedure
Execution - Sample 1
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
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 :
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.