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