DBA Tips Archive for Oracle
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 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.
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:
The following source code provides an example C
shell.c which contains
two subroutines. The first subroutine, named
mailx, will send an email message
using the provided parameters. The second
sh, will run
an O/S command that is passed in as a parameter.
Issue the following commands to compile the code and generate the shared library object:
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
Log in as the application user and create the library definition
CREATE LIBRARY command. Note that the
directory in quotes is the current of location of the library that
was created in the steps above.
As the application user, log in to the database and create all PL/SQL specification procedures.
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.
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 server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, 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 20 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 and Mathematics.
Copyright (c) 1998-2014 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 firstname.lastname@example.org.
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: 93092