HOW TO CALL AN EXTERNAL C FUNCTION FROM WITHIN ORACLE

How to call an external C function from within Oracle

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

One very useful feature of Oracle is its ability to call external C procedures from within the database.
It's handy for creating interfaces to legacy systems.
The following procedure will demonstrate how to compile a simple C program, and how to configure the database to call it.
The C program will contain a single procedure for converting strings into uppercase.

Several layers of objects and configuration will be required to make this work:
C Program/library
(andy_lib.so)
   |
Listener
(extproc)
   |
Database library object
(andy_lib)
   |
Database function
(andy_lib_upper)

This might seem a little daunting and over complicated, but it's actually quite logical and very simple.
To simplify the process, we will construct the test case in the same order as the diagram above.

To build this example you will need the following:
An oracle database (9i or later)
A working listener configuration
C compiler (gcc is used in the example)
A database user with 'create procedure' and 'create library' privileges

STEPS
--------

1. Create the C program
Using a text editor, create a file named andy_lib.c and paste the following C code into it:
#include <ctype.h>
int andy_upper(char *istr, char *ostr)
{
    int i = 0;
    while(istr[i])
   {
      ostr[i] = toupper(istr[i]);
      i++;
   }
   return 0;
}

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

2. Compile the shared library and copy it to the Oracle home
To compile and link the library run these commands:

gcc -c andy_lib.c

ld -shared -o andy_lib.so andy_lib.o

The linker will probable create the library with rather excessive permissions.
I'd advise changing them:

chmod 600 andy_lib.so

Now, copy the shared library to the Oracle home:

cp andy_lib.so $ORACLE_HOME/bin/

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

3. Configure the listener and tnsnames
The listener will need to be configured to handle external procedures (extproc). My listener.ora looks like this:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = fermat)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)

You will also need to add the following entry to your tnsnames.ora:

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

Restart the listener and test the configuration with tnsping:

lsnrctl stop;
lsnrctl start;
tnsping EXTPROC_CONNECTION_DATA

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

4. Create a database library object

The library object is a pointer to the library file that we copied into the Oracle home.
Run the following SQL to create it:

sql>create or replace library andy_lib
    as '/app/oracle/product/11.2.0/bin/andy_lib.so';
    /

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

5. Create a database function

The C library in this example contains a single function, but in reality your library might contain several.
We need to create function objects that map onto each of the procedures in the C library:

create or replace function andy_lib_upper(p_istr in varchar2, p_ostr out varchar2)
 return binary_integer
 as external
library andy_lib
name "andy_upper"
language c
parameters (p_istr string, p_ostr string);
/

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

6. Test it

Cut and paste the following pl/sql into sqlplus.
It will pass a lowercase string into the C function...

set serveroutput on size 9999
declare
res binary_integer;
v_in CHAR(100);
v_out CHAR(100);
begin
v_in := 'hello world';
res := andy_lib_upper(v_in, v_out);
dbms_output.put_line(res);
dbms_output.put_line(v_in);
dbms_output.put_line(v_out);
end;
/

it works you will see the following:
hello world
HELLO WORLD
-------------------------------------------------------------------------
----------------------------*****************------------------------