oracle使用外部库函数

p, li { white-space: pre-wrap; }

1、监听里有IPC协议

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

)

ADR_BASE_LISTENER = /u01/app/oracle

tnsnames.ora上添一个ipc名

EXTPROC01 =

(DESCRIPTION=

(ADDRESS=(PROTOCOL=ipc)(key=EXTPROC1521))

(CONNECT_DATA=(SERVICE_NAME=orcl))

)

2、创建一个语言

cat test.c

#include <stdio.h>

test(n)

int n;

{

int lucky_money;

lucky_money=100*n;

return (lucky_money);

}

生成动态so库，需要添加-fPIC -shared两个参数

gcc -o libtest.so -fPIC -shared test.c

3、把外部库复制到ORACLE_HOME目录下，修改权限

[root@ora11 ~]# cp libtest.so /u01/app/oracle/product/11.2.0/dbhome_1/bin/

[root@ora11 ~]# cd /u01/app/oracle/product/11.2.0/dbhome_1/bin/

[root@ora11 bin]# chown oracle:oinstall libtest.so

[root@ora11 bin]# ll libtest.so

-rwxr-xr-x 1 oracle oinstall 7880 May 12 07:45 libtest.so

4、创建library

create or replace library test_code as '/u01/app/oracle/product/11.2.0/dbhome_1/bin/libtest.so'

/

用户赋外部表执行权限

grant EXECUTE on sys.test_code to test; 给test

4、创建内部函数

create or replace function func_test

(x binary_integer)

return binary_integer

as language C

library sys.test_code

name "test";

/

5、验证

set serveroutput on

var lucky_money number;

var amount number;

exec :lucky_money :=8888;

exec :amount := func_test(8888);

print amount;