oracle测试调用过程,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/，如需转载，请注明出处，否则将追究法律责任。