It is possible to use external library
procedures within PL/SQL. If external procedures are written in Java language,
it is very easy. You can create and compile a java source inside the database
via DDL commands. But if you have to call some C procedures, you cannot do it as
easy as Java sources. First, you have to create a C procedure, compile it with
proper compiler, generate shared object (so) with necessary parameters and copy
it to $ORACLE_HOME/lib ($LD_LIBRARY_PATH). Up to now, the steps should be
followed in the server side, not in the database. Then, database steps (DDL
commands) should be executed: create a library object and a wrapper PL/SQL function
to call external C procedure. For java sources, there is no need for creating
library object.
Prior to Oracle 11G, external procedures
need a special network configuration, extproc
with IPC protocol. With 11G, you do not need to make this tns configuration in
most situations.
I will demonstrate, how to create a simple
C procedure to get value of environment variable value and execute and get
response of system commands with PL/SQL functions. Please note that, you can do
most of these requirements with java sources. Note also that, C procedures below
are intended to be use in simulation purposes. You may need to add some more
lines of codes for your reqs.
I did not write any C programs almost for nine
years. I remember my old-school days and how difficult I understand the
pointers concepts in C languageJ. At least ten times I read Chapter 5 (or 6) - Pointers from “C How
to Program” (Deitel pub.) J….
1.
First make necessary Oracle network
configuration. You can use netmgr
tool or do it by handJ.
listener.ora before change
==================================
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
0.0.0.0)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
0.0.0.0)(PORT = 80))
(PROTOCOL_STACK =
(PRESENTATION = HTTP)
(SESSION = RAW)
)
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
0.0.0.0)(PORT = 21))
(PROTOCOL_STACK =
(PRESENTATION = FTP)
(SESSION = RAW)
)
)
)
listener.ora after change
==================================
SID_LIST_LISTENER
=
(SID_LIST =
(SID_DESC =
(PROGRAM = extproc)
(SID_NAME = extproc)
(ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_2)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
0.0.0.0)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
0.0.0.0)(PORT = 80))
(PROTOCOL_STACK =
(PRESENTATION = HTTP)
(SESSION = RAW)
)
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
0.0.0.0)(PORT = 21))
(PROTOCOL_STACK =
(PRESENTATION = FTP)
(SESSION = RAW)
)
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY =
extproc))
)
)
tnsnames.ora entry
==================================
EXTPROC_CONNECTION_DATA=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))
(CONNECT_DATA=
(SID=extproc)))
2.
Create a C program (c_system_lib.c)
that fulfills our needs.
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
char* execute_command( char *inputCommandText )
{
FILE *fp;
char temp[10000];
char commandReturnText[1000000];
char *commandReturnPointer;
memset(&commandReturnText[0], 0, sizeof(commandReturnText));
fp = popen(inputCommandText, "r");
if (fp == NULL) {
commandReturnPointer = "ERROR:Unable
to popen...";
}
else{
while (fgets(temp, sizeof(temp)-1, fp) != NULL) {
strcat (commandReturnText,temp);
}
pclose(fp);
}
commandReturnPointer = commandReturnText;
return commandReturnPointer;
}
char* get_env_value( char *environmentValueName )
{
char *environmentValue;
environmentValue
= getenv(environmentValueName);
return environmentValue;
}
3.
Compile c_system_lib.c with
proper compiler, generate shared object (so) with necessary parameters and copy
it to $ORACLE_HOME/lib ($LD_LIBRARY_PATH).
[oracle@dhcppc5 admin]$
uname -a
Linux dhcppc5
2.6.18-194.17.1.0.1.el5 #1 SMP Wed Sep 29 15:40:03 EDT 2010 i686 i686 i386
GNU/Linux
[oracle@dhcppc5
sample_c_ext]$ gcc -c c_system_lib.c
[oracle@dhcppc5
sample_c_ext]$ ld -shared -melf_i386 -o c_system_lib.so c_system_lib.o
[oracle@dhcppc5
sample_c_ext]$ chmod 755 c_system_lib.so
[oracle@dhcppc5
sample_c_ext]$ cp c_system_lib.so $ORACLE_HOME/lib
4.
Create a library object (c_system_lib)
and two wrapper PL/SQL functions(ExecuteShellCommandViaC and GetEnvValueViaC)
to call external C procedures:
CREATE LIBRARY
c_system_lib is '/home/oracle/app/oracle/product/11.2.0/dbhome_2/lib/c_system_lib.so';
/
CREATE OR REPLACE FUNCTION
ExecuteShellCommandViaC(pis_CommandString IN VARCHAR2)
RETURN VARCHAR2 AS EXTERNAL
NAME "execute_command"
LIBRARY c_system_lib
LANGUAGE C
PARAMETERS (pis_CommandString string,RETURN string);
/
CREATE OR REPLACE FUNCTION
GetEnvValueViaC(pis_EnvironmentVariableName IN VARCHAR2)
RETURN VARCHAR2 AS EXTERNAL
NAME "get_env_value"
LIBRARY c_system_lib
LANGUAGE C
PARAMETERS (pis_EnvironmentVariableName string,RETURN string);
/
5.
Test the wrapper functions:
select GetEnvValueViaC('ORACLE_HOME') from dual;
GETENVVALUEVIAC('ORACLE_HOME')
--------------------------------------------------------------------------------
/home/oracle/app/oracle/product/11.2.0/dbhome_2
select ExecuteShellCommandViaC('/bin/ls /home/oracle') from dual;
EXECUTESHELLCOMMANDVIAC('/BIN/
--------------------------------------------------------------------------------
apex
apexlistener.sh
app
dav
deneme.txt
Desktop
emshutdown
emstartup
fix.sql
jdeveloper
listener
oracle
oracle_db11g_clr.gif
oradiag_oracle
otn_logo_small.gif
otn_new.css
repos
reset_apex
reset_imdbcache
reset.log
reset_OE.sql
reset_sqldev
reset_xdbPorts.sql
reset_xmldb
shrink.sh
shutdown
StartHere2010.htm
start_timesten
startup
status
stop_timesten
svn_repo
temp_dir
tmp
Please note that DBMS_SYSTEM.get_env
procedure can also show envrionment variable values; but DBMS_SYSTEM package
does not recommended for customer use. By default, there is no synonym for DBMS_SYSTEM
supplied package.
DECLARE
vs_OracleHome VARCHAR2(100);
BEGIN
sys.dbms_system.get_env('ORACLE_HOME', vs_OracleHome);
dbms_output.put_line(vs_OracleHome);
END;
/
