How to call an external C function from within Oracle

From : http://www.shutdownabort.com

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
  • 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 = /app/oracle/product/10.2.0.3)
          (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:

    create or replace library andy_lib
    as '/app/oracle/product/10.2.0.3/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;
    /

    If it works you will see the following:

    hello world
    HELLO WORLD

    If it doesn’t work, you will most likely get one of these two errors:

    ERROR at line 1:
    ORA-28575: unable to open RPC connection to external procedure agent
    ORA-06512: at "ANDY.ANDY_LIB_UPPER", line 1
    ORA-06512: at line 8

    Or..

    ERROR at line 1:
    ORA-28595: Extproc agent : Invalid DLL Path
    ORA-06512: at "ANDY.ANDY_LIB", line 1
    ORA-06512: at line 8

    Go back and make sure that you completed all of the steps properly. Pay particular attention to the listener and tnsnames configuration. If it all looks ok, try restarting the database and listener. Make sure the listener is running before the database is started – it seems to be quite fussy about that

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: