很多功能如果直接通过plsql实现可能会很困难,如通信方面的一些功能等,如果要通过C/C++实现的话可能会非常容易,
oracle在plsql中支持调用C/C++写的函数。实现过程大致如下:(声明:本人也没有在实际应用中使用过external procedure,下面仅是一个大致的测试过程),有关external procedure的原理以及更详细的了解可以参考oracle doc。
--在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>
--在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/7425000/viewspace-733580/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7425000/viewspace-733580/