Oracle DBA Tips Corner |
|
Calling OS Commands from PL/SQL using External Procedures - (extproc)
by Jeff Hunter, Sr. Database Administrator
Oracle provides the ability to call operating system commands from PL/SQL with a new feature called external procedures. The external procedures feature was added in Oracle8. This month I will give an overview of the steps involed in setting up external procedures. These examples where created on Solaris and tested with 8.0.4, 8.0.5 and 8.1.6.
If you are using Oracle9i, please read my article entitled "Changes in Configuring External Procedures in Oracle 9.2.0". It includes several security changes that have to be configured to enable PL/SQL External Procedures.
The following represents a typical listener.ora configuration. Details for your particular installation may vary.
Listing 1 - Sample listener.ora file
listener.ora Configuration LISTENER = (ADDRESS_LIST = (ADDRESS= (PROTOCOL= IPC)(KEY=EXTPROC)) (ADDRESS= (PROTOCOL= TCP)(HOST=testdb)(PORT=1521)) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = ORA816) (ORACLE_HOME = /u01/app/oracle/product/8.1.6) ) (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/8.1.6) (PROGRAM = extproc) ) ) STARTUP_WAIT_TIME_LISTENER_PROC = 0 CONNECT_TIMEOUT_LISTENER_PROC = 10 TRACE_LEVEL_LISTENER_PROC = OFF
The following represents a typical tnsnames.ora configuration. Details for your particular installation may vary:
Listing 2 - Sample tnsnames.ora file
tnsnames.ora EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) (CONNECT_DATA = (SID = PLSExtProc)(SERVER=DEDICATED)) ) ORA816= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(Host = testdb)(Port = 1521)) (CONNECT_DATA = (SID = ORA816)) )
Listing 3 - Sample external procedure code
shell.c #include<stdio.h> #include<stdlib.h> #include<string.h> void mailx(char *to, char *subject, char *message) { int num; char command[50000]; strcpy(command, "echo \""); strcat(command, message); strcat(command, "\" | mailx -s \""); strcat(command, subject); strcat(command, "\" "); strcat(command, to); num = system(command); } void sh(char *command) { int num; num = system(command); }Issue the following commands to compile the code and generate the shared object in Solaris:
gcc -G -c shell.c ld -r -o shell.so shell.o chmod 775 shell.soYou can also use the demo_rdbms.mk makefile 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:
- In 8.0.X,
$ make -f demo_rdbms.mk extproc_nocallback \ SHARED_LIBNAME=shell.so OBJS=shell.o- In 8.1.X,
$ make -f demo_rdbms.mk extproc_no_context \ SHARED_LIBNAME=shell.so OBJS=shell.o
CREATE LIBRARY shell_lib is '/u01/app/oracle/c/shell.so'; /Note: The directory in quotes is the current of location of the library that was created in the steps above.
Listing 4 - Sample PL/SQL Wrapper Procedure
Create PL/SQL Wrapper Procedures CREATE OR REPLACE PROCEDURE shell(command IN char) AS EXTERNAL NAME "sh" LIBRARY shell_lib LANGUAGE C PARAMETERS (command string); / CREATE OR REPLACE PROCEDURE mailx(send_to IN char, subject IN char, message IN char) AS EXTERNAL NAME "mailx" LIBRARY shell_lib LANGUAGE C PARAMETERS (send_to string, subject string, message string); /
SQL> exec shell('ls'); cli.trc exe_prba.sql prueba.mk shell.c.old core listener.old prueba.o shell.o dec2bin.c listener.ora prueba.so shell.so dec2bin.c.old nena.lst prueba.sql shell.sql dec2bin.o p.sql sal.1 shell.sql.old dec2bin.so pepito.lst sal.2 sqlnet.log dec2bin.sql prb sal.3 tnsnames.ora dec2bin.sql.old prb.c salida.lst tnsnames.ora.old envoltorio.sql prueba.c shell.c uno.sql PL/SQL procedure successfully completed. SQL> exec mailx('Jeffrey.Hunter@marconi.com', 'EXTPROC Test', 'This is a test'); 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.
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.
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter
and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express,
prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate,
but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from
relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.