使用Oracle的外部调用大致分以下几步：
1、编写共享库（或动态连接库），也就是你想调用的过程或函数。
2、设置listener.ora和tnsnames.ora，确保外部调用的服务可用。
3、create library
4、create function or procedure
下面的例子是在solaris 7 + oracle 816环境下通过的,只是写一个随机函数。
1、编写共享库：
test.c:
#include
#include
int ora_rand()
{
int rao;
rao = rand();
return rao;
}
编译：
$ gcc -c test.c
$ ls
test.c test.o
$ ld -G test.o -o test.so
$ ls
test.c test.o test.so
2、配置listener.ora和tnsnames.ora
listener.ora:
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.99)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /home/oracle/prodUCt/816)
(PROGRAM = extproc)
)
(SID_DESC =
(ORACLE_HOME = /home/oracle/product/816)
(SID_NAME = sid1)
)
)
tnsnames.ora:
# Generated by Oracle configuration tools.
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
sid1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.99)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sid1)
)
)
当然文件都是本地的
3、create library:
create library 要有相应的权限，我为了省事，就在system用户下建的：
SQL CREATE OR REPLACE LIBRARY ext_lib IS '/home/oracle/local/test.so'
;
2 /
Library created.
4、create function:
SQL create function test_rand
2 return binary_integer as language c
3 name "ora_rand"
4 library ext_lib;
5 /
Function created.
然后就可以调用test_rand生成随机数了：
SQL select 1*test_rand from dual;
1*TEST_RAND
-----------
16838
SQL /
1*TEST_RAND
-----------
5758
SQL /
1*TEST_RAND
-----------
10113
这个函数生成的随机数在0到32767之间。
以上只是一个很简单的例子，只要把握了方法，理论上就可以用PL/SQL做任何事了