external procedure调用的大致测试过程！

很多功能如果直接通过plsql实现可能会很困难，如通信方面的一些功能等，如果要通过C/C++实现的话可能会非常容易，oracle在plsql中支持调用C/C++写的函数。实现过程大致如下：（声明：本人也没有在实际应用中使用过external procedure，下面仅是一个大致的测试过程），有关external procedure的原理以及更详细的了解可以参考oracle doc。[@more@]

--在listener中做如下配置
[oracle@linuxrac2 admin]$ more listener.ora
# LISTENER.ORA.LINUXRAC2PUB Network Configuration File: /opt/oracle/product/9.2.
0/network/admin/listener.ora.linuxrac2pub
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY=EXTPROC))
(ADDRESS = (PROTOCOL = TCP)(HOST = linuxrac2pub)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/product/9.2.0)
(PROGRAM = extproc)
) (SID_DESC =
(ORACLE_HOME = /opt/oracle/product/9.2.0)
(SID_NAME = racdb1)
)
)
--=========================================
在服务器的tnsnames.ora中要保证有下面一段：
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
--==========================================
--并且测试一下要保证网络畅通。
[oracle@linuxrac2 admin]$
[oracle@linuxrac2 admin]$ tnsping EXTPROC_CONNECTION_DATA

TNS Ping Utility for Linux: Version 9.2.0.4.0 - Production on 04-FEB-2009 14:30:48

Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO)))
OK (30 msec)
--===========================================
[oracle@linuxrac2 admin]$ cd /home/oracle/extproc/
[oracle@linuxrac2 extproc]$ pwd
/home/oracle/extproc
[oracle@linuxrac2 extproc]$ ls
--编写一C函数,功能及其简单，给传入的参数值×100返回结果
[oracle@linuxrac2 extproc]$ vi extproc_test.c

extproc_test(n)
int n;
{
int m;
m=n*100;
return (m);
}

"extproc_test.c" [New] 8L, 59C written
[oracle@linuxrac2 extproc]$ ls
extproc_test.c
--将C函数编译成动态连接库
[oracle@linuxrac2 extproc]$ cc -shared -o extproc_test.so extproc_test.c
[oracle@linuxrac2 extproc]$ ls
extproc_test.c extproc_test.so
--将编译好的动态连接库文件拷贝到$ORACLE_HOME/bin目录下
[oracle@linuxrac2 extproc]$ cp extproc_test.so $ORACLE_HOME/bin
[oracle@linuxrac2 extproc]$
[oracle@linuxrac2 extproc]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Feb 4 14:35:47 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
--然后在sqlplus中创建一个library，用来说明要调用的C函数所在的库文件的位置及名称
SQL> create or replace library extproc_c_code as '$ORACLE_HOME/bin/extproc_test.so';
2 /

Library created.
SQL> create user test identified by test;

User created.

SQL> grant dba to test;

Grant succeeded.
--把创建好的library的execute权限授予要执行外部过程的用户
SQL> grant execute on extproc_c_code to test;

Grant succeeded.

SQL> connect test/test
Connected.
--要使用这个C函数，还必须在数据库里创建一个声明调用，如下：
SQL> create or replace function call_extproc_c(x binary_integer)
2 return binary_integer
3 as language C
4 library sys.extproc_c_code
5 name "extproc_test";
6 /

Function created.

SQL> set serveroutput on
SQL> var i number;
SQL> var j number;
SQL> exec :i :=100;

PL/SQL procedure successfully completed.

SQL> exec :j := call_extproc_c(:i);

PL/SQL procedure successfully completed.

SQL> print j

J
----------
10000

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19602/viewspace-1016717/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19602/viewspace-1016717/