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

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值