DBA Tips Archive for Oracle

  

Calling OS Commands from PL/SQL using External Procedures - (extproc)

by Jeff Hunter, Sr. Database Administrator

Contents

Introduction

Oracle provides the ability to call operating system commands from PL/SQL with a feature called external procedures. The external procedures feature was first introduced in Oracle8.

When calling an external routine from inside of Oracle PL/SQL, the program must run as a shared library. This type of program in Linux, Solaris, and AIX is known as a shared object with a .so file extension. Under HP-UX, the program is called a shared library with an .sl file extension while on the Microsoft operating system, the program is a DLL file (dynamically linked library). In general, the program can be written in any language as long as the compiler and linker can generate the appropriate shared library format that is callable from C. This includes C, C++, FORTRAN, COBOL, Visual Basic, and Java.

Once the shared library is generated, the external program is published by writing a special type of PL/SQL wrapper known as a call specification. If the external routine returns a value, it maps to a PL/SQL function. However, if the external routine returns no value, it is mapped to a PL/SQL procedure.

This article provides an overview of the steps involved in setting up external procedures using PL/SQL and the UNIX operating environment.

 

If you are using Oracle9i or higher, 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.

listener.ora

The following represents a typical listener.ora configuration. Details for your particular installation may vary. 


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
      (ENVS="EXTPROC_DLLS=ONLY:/u01/app/oracle/dba_scripts/extproc/shell.so")
    )
    (SID_DESC =
      (GLOBAL_DBNAME = alexdb.idevelopment.info)
      (SID_NAME = alexdb)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
    )
  )

INBOUND_CONNECT_TIMEOUT_LISTENER = 0

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = alex.idevelopment.info)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

tnsnames.ora

The following represents a typical tnsnames.ora configuration. Details for your particular installation may vary: 


ALEXDB.IDEVELOPMENT.INFO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = alex.idevelopment.info)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ALEXDB.IDEVELOPMENT.INFO)
    )
  )

EXTPROC_CONNECTION_DATA.IDEVELOPMENT.INFO =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

External Procedure Source Code

The following source code provides an example C program named shell.c which contains two subroutines. The first subroutine, named mailx, will send an email message using the provided parameters. The second subroutine, named sh, will run an O/S command that is passed in as a parameter. 


/*
 * shell.c
 * Example program used to demonstrate how to call O/S
 * commands from PL/SQL using external procedures. 
 *
 */

#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 library object:

Solaris


-bash-3.00$ gcc -m64 -fPIC -c shell.c
-bash-3.00$ gcc -m64 -shared -static-libgcc -o shell.so shell.o
-bash-3.00$ chmod 775 shell.so

or 


-bash-3.00$ gcc -G -c shell.c
-bash-3.00$ ld -r -o shell.so shell.o
-bash-3.00$ chmod 775 shell.so

Linux


[oracle@racnode2 extproc]$ gcc -fPIC -c shell.c
[oracle@racnode2 extproc]$ gcc -shared -static-libgcc -o shell.so shell.o
[oracle@racnode2 extproc]$ chmod 775 shell.so

or 


[oracle@racnode2 extproc]$ gcc -fPIC -DSHARED_OBJECT -c shell.c
[oracle@racnode2 extproc]$ ld -shared -o shell.so shell.o
[oracle@racnode2 extproc]$ chmod 775 shell.so

Oracle Makefile

Oracle provides the demo_rdbms.mk makefile that can be used 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:

Oracle 8.0.X


-bash-3.00$ make -f demo_rdbms.mk extproc_nocallback SHARED_LIBNAME=shell.so OBJS=shell.o

Oracle 8.1.X


-bash-3.00$ make -f demo_rdbms.mk extproc_no_context SHARED_LIBNAME=shell.so OBJS=shell.o

Library Definition

Log in as the application user and create the library definition using the CREATE LIBRARY command. Note that the directory in quotes is the current of location of the library that was created in the steps above. 


CREATE LIBRARY shell_lib is '/u01/app/oracle/dba_scripts/extproc/shell.so';
/

Library created.

PL/SQL Wrapper Procedure

As the application user, log in to the database and create all PL/SQL specification procedures. 


CREATE OR REPLACE PROCEDURE shell(command IN char)
  AS EXTERNAL
     NAME "sh"
     LIBRARY shell_lib
     LANGUAGE C
     PARAMETERS (command string);
/

Procedure created.


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

Procedure created.

Execution

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.

By default, 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. 


SQL> exec mailx('jhunter@idevelopment.info', 'EXTPROC Test', 'This is a test');

PL/SQL procedure successfully completed.



SQL> exec shell('ls /u01/app/oracle/admin/alexdb/udump > /export/home/oracle/myoutput.txt');

PL/SQL procedure successfully completed.

SQL> !cat /export/home/oracle/myoutput.txt
alexdb_ora_1019.trc
alexdb_ora_10409.trc
alexdb_ora_1047.trc
alexdb_ora_1112.trc
alexdb_ora_1155.trc
alexdb_ora_1295.trc
alexdb_ora_1371.trc
alexdb_ora_1609.trc
alexdb_ora_1651.trc
alexdb_ora_2008.trc
alexdb_ora_5541.trc
alexdb_ora_5569.trc
alexdb_ora_5602.trc
alexdb_ora_5617.trc
alexdb_ora_5643.trc
alexdb_ora_5651.trc
alexdb_ora_5657.trc
alexdb_ora_5658.trc
alexdb_ora_5686.trc
alexdb_ora_5730.trc
alexdb_ora_5786.trc
alexdb_ora_5787.trc
alexdb_ora_5815.trc
alexdb_ora_814.trc
alexdb_ora_826.trc
alexdb_ora_842.trc
alexdb_ora_881.trc
alexdb_ora_892.trc
alexdb_ora_966.trc
alexdb_ora_977.trc

About the Author

Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX, Linux, and Windows server environment. Jeff's other interests include mathematical encryption theory, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 18 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science.


Copyright (c) 1998-2013 Jeffrey M. Hunter. All rights reserved.

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.

Last modified on
Sunday, 18-Mar-2012 22:29:12 EDT
Page Count: 86812