Create and Run a Sample External Procedure Program on Linux

Today I need to write external procedure:

I have used metalink Note:312564.1

I use Oracle 11g on linux x86_64:

I assumed ORACLE_HOME is /oracle/11g PATH.

1. I need ensure extproc be work, So I modified listener.ora and tnsnames.ora files in $ORACLE_HOME/network/admin PATH

listener.ora =>
---------------------------------------------------------------
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC=
(SID_NAME=PLSExtProc)
(ORACLE_HOME=/oracle/11g)
(PROGRAM=extproc)
(ENVS="EXTPROC_DLLS=ANY")
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
)

---------------------------------------------------------------

and then reload listener + check

$ lsnrctl reload

LSNRCTL for Linux: Version 11.1.0.6.0

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver)(PORT=1521)))

The command completed successfully

$ lsnrctl services

Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER

tnsnames.ora =>

---------------------------------------------------------------

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL = IPC)( KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = PLSExtProc)
)
)

---------------------------------------------------------------

Check KEY (EXTPROC1521) and SID (PLSExtProc) on tnsnames.ora file with listener.ora file

And then test extproc configure:

$ tnsping EXTPROC_CONNECTION_DATA

TNS Ping Utility for Linux: Version 11.1.0.6.0

Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS=(PROTOCOL = IPC)( KEY = EXTPROC1521))) (CONNECT_DATA = (SID = PLSExtProc)))
OK (0 msec)


2. created file with C code (shell.c)

#include < stdio.h >
#include < stdlib.h >
#include < string.h >

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

3. compiled code and generated shared object

Anyway I checked extproc version:

$ $ORACLE_HOME/bin/extproc

Oracle Corporation

Heterogeneous Agent Release 11.1.0.6.0 - 64bit Production

Using the table below,

Operating System	extproc bit version	1st: Compile the shell.c program	2nd: Generate the shared object
Solaris SPARC	extproc (64 bit)	gcc -G -c -m64 shell.c	ld -r -o shell.so shell.o
Solaris SPARC*	extproc32 (32 bit)	gcc -G -c shell.c	ld -r -o shell.so shell.o
Linux RH AS3 32 bit	extproc (32 bit)	gcc -c shell.c	ld -shared -o shell.so shell.o
Linux RH AS4 x86-64	extproc (64 bit)	gcc -fPIC -c shell.c	ld -shared -o shell.so shell.o
Linux RH AS4 x86-64*	extproc32 (32 bit)	gcc -m32 -c shell.c	ld -shared -melf_i386 -o shell.so shell.o
AIX 5L	extproc (64 bit)	gcc -maix64 -c shell.c	gcc -maix64 -shared -o shell.so shell.o
AIX 5L*	extproc32 (32 bit)	gcc -c shell.c	gcc -shared -o shell.so shell.o
HPUX 11.11**	extproc32 (32 bit)	gcc -c shell.c	gcc -shared -o shell.sl shell.o
HPUX 11.11	extproc (64 bit)	/usr/local/pa64/bin/gcc -c shell.c	/usr/local/pa64/bin/gcc -shared -o shell.sl shell.o

and then compiled:

This case used Linux RH AS4 x86-64, extproc (64 bit)

$ gcc -fPIC -c shell.c

$ ld -shared -o shell.so shell.o

$ chmod 755 shell.so

$ cp shell.so $ORACLE_HOME/lib

4. created library and procedure

$ sqlplus / as sysdba

SQL> CREATE or replace LIBRARY shell_lib is '$ORACLE_HOME/lib/shell.so'

/

Library created.

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

/

Procedure created.

5. Test

SQL> exec shell('ls');

PL/SQL procedure successfully completed.

Didn't see anything, So

SQL> exec shell('ls > /tmp/test01');

PL/SQL procedure successfully completed.

$ cat /tmp/test01

shell.c
shell.o
shell.so

extproc is functional is work.


Enjoy!