Steps to Create and Run a Sample External Procedure Program(OS command)

Doc ID: Note:312564.1 Type: HOWTO
Last Revision Date: 03-MAY-2006 Status: PUBLISHED

(OS command)
The information in this document applies to:
Oracle Net Services - Version: 8.1.7.4 to 10.1.0.4
Information in this document applies to any platform.
Prerequisites: a C compiler (This note is written with Solaris and Linux compilers gcc and cc)
Goal
The goal of this document is to create a sample program that calls OS Commands from PL/SQL using extproc.

The Oracle demos for extproc depend on the scott schema being installed as well as the sample "emp" and "dept" tables being created. Most customers do not choose to utilize either option (or both) at install time thereby resulting in no efficient way to test extproc and its configuration.

The information provided in this note is a quick way of testing extproc (or extproc32) and should work with all versions of Oracle on any platform. Only steps 6 and 7 are specific to Sun and Linux. If you are using a different OS, it is your responsibility to find and correctly use a C compiler and a link editor.

Solution
1. Configure the Oracle configuration files.

Following these steps exactly will ensure extproc will work. While you may have different listener.ora and tnsnames.ora files, remember, the goal here is to get extproc working. After that, a custom configuration can be put in place. If problems arise, this base configuration can be revisited for reference.

In the $ORACLE_HOME/network/admin directory, backup any current listener.ora file and create a new file with these contents:


listener_extproc=
(address=(protocol=ipc)(key=extproc))
sid_list_listener_extproc=
(sid_list=
(sid_desc=
(sid_name=extproc)
(oracle_home=)
(program=extproc)
)
)
NOTE: If using extproc32, set (program=extproc32)


In the $ORACLE_HOME/network/admin directory, backup any current tnsnames.ora file and create a new file with these contents:


extproc_connection_data=
(description=
(address=(protocol=ipc)(key=extproc))
(connect_data=(sid=extproc)))

In the $ORACLE_HOME/network/admin directory, move any existing sqlnet.ora file to sqlnet.bak

2. Set the TNS_ADMIN environment variable.

In the $ORACLE_HOME/network/admin directory, issue the command:

For k shell

export TNS_ADMIN=`pwd`
For c shell

setenv TNS_ADMIN `pwd`
NOTE: the ` character surrounding `pwd` is the grave accent key, usually to the left of the "1" key on a standard 107 key PS/2 keyboard. It is also the paired on the same key as the tilde (~).

3. Start the listener.

lsnrctl start listener_extproc
4. Use tnsping to test the extproc configuration. This step has to succeed.

tnsping extproc_connection_data
If an error message is received, revisit steps 5-7 or contact support

5. Create a file with the following C code (it is called shell.c for this note)


#include
#include
#include
void sh(char *command) {
int num;
num = system(command);
}

6. Compile the code

For Solaris: To create a 64 bit file for extproc, use:

gcc -G -c -m64 shell.c
For Solaris: To create a 32 bit file for extproc32, use:

gcc -G -c shell.c
For Linux 32bit: To create a 32 bit file for extproc, use:

cc -c shell.c
7. Generate the shared object and change permissions

Solaris:

ld -r -o shell.so shell.o
chmod 775 shell.so
Linux 32bit:

ld -shared -o shell.so shell.o
chmod 775 shell.so
8. Place shell.so in the $ORACLE_HOME/lib directory.

This will bypass the need for adding ENVS and EXTPROC_DLLS parameters to the listener.ora file. Remember, tweaking can be done later; for now, we want the configuration to be as simple as possible.

9. Create a LIBRARY definition in Oracle

Logon to Oracle using a common user without using Oracle Net:

sqlplus system/manager

CREATE LIBRARY shell_lib is '/lib/shell.so';
/


10. Create the PL/SQL Wrapper(This procedure can be used in single line mode,
create a function instead if you want to use in pl/sql block)



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


11. Test the stored procedure:

exec shell('ls');
All you should see is "PL/SQL procedure successfully completed." which is good enough for this test and proves extproc is working properly. To see the output of the OS command passed to extproc, redirect the output to a text file:

exec shell('ls>output.txt');
A file called "output.txt" will be createdhich contains the output of the 'ls' command.

If there is no output, supply a full path to an output file:

exec shell('ls>/u01/app/oracle/output.txt');

At this point, extproc is functional and is proven to work. Customization can now take place.


============================================================================

Thanks goes to an internet forum posting by J. Hunter, Sr. Database Administrator, for providing the shell code and the correct command syntax for many of the commands.