oracle 11g PL/SQL Programming学习十三

----------------------------------------------------------------------------
-----------------PL/SQL学习笔记系列 By Cryking-----------------
------------------------转载请注明出处,谢谢!------------------------

第13章 外部存储(External Procedures)

一般日常的外部操作都通过外部存储处理.这样数据库可通过PL/SQL来和外部应用进行交流.
开发人员可能想数据库少一些逻辑编程,所以外部存储是常用的选择.
这对密集型计算程序是理想的,此时的外部存储提供了一个外部数据资源到数据库的接口.
不像单独的Oracle Prc*C程序,外部存储能被PL/SQL调用.
在本章,你将会用到c共享库和JAVA类库.

1.外部存储架构定义
Oracle为外部存储构建了一个可扩展的架构.
它能非常灵活的支持任何能被C调用的编程语言.
例如,你可以在C中调用C++的程序(通过extern命令),尽管这样,通过外部程序回调到数据库时仍需要OCI的支持.
OCI支持C,C++,COBOL,FORTRAN,PL/1,Visual Basic,Perl,PHP和Java.
无论你选择了哪种语言来执行,必须支持建立一个共享库,也就是DLL.
JAVA的共享库称为单元库.当你从PL/SQL访问共享库时,库被作为外部存储动态装载到内存.
缺省情况下,每一个远程存储调用使用分散的、专有的extproc代理去访问共享库.
你也可以通过Oracle异构服务(ORACLE HS)选择配置一个多线程代理.如果这样,你可以在任何数据库会话之间共享extproc代理.
外部存储使用PL/SQL定义库来在PL/SQL运行时引擎和共享库之间交换数据.
PL/SQL定义库定义了指定的外部调用,并且映射PL/SQL数据类型到本地语言的数据类型.

2.定义extproc oracle网络服务配置
外部存储使用oracle网络服务来fork或链接到extproc代理.
extproc代理可以是默认的独立单元或者一个多线程的extproc代理.
麻烦的是,配置你的listener.ora和tnsnames.ora将是一个手动的过程.
使用NETCA建立标准的监听并不提供一个完整的extproc代理监听器.
你必须为外部存储建立一个专用的监听.

标准的listener.ora文件包含两块内容:LISTENER以及SID_LIST_LISTENER.
LISTENER描述了一个地址列表或一组地址列表.
地址由一个协议和一个key值组成,或者一个协议、主机名、端口号组成.
11g标准的LISTENER格式块如下:

LISTENER =
 (DESCRIPTION_LIST =
  (DESCRIPTION =
   (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)
          (KEY = EXTPROC1521)
    )
   )
   (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)
          (HOST = <host_name>.<domain_name>)
          (PORT = 1521)
    )
   )
  )
)

标准的listener.ora文件不能支持extproc代理,因为它有两个不同协议的ADDRESS_LIST.
SID_LIST_LISTENER部分包含SID的描述.
标准的SID_LIST_LISTENER块包含SID_NAME,ORACLE_HOME和PROGRAM预定义参数.
标准的SID_LIST_LISTENER块格式如下:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = <oracle_home_directory)
(PROGRAM = extproc)
)
)
SID_NAME和PROGRAM参数在这里可以用来支持extproc代理.

标准listener.ora文件和标准tnsnames.ora文件配合,一起为oracle网络服务所使用.
标准的tnsnames.ora文件提供两个服务名:
一个是代码,用来映射数据库的标准监听.
另一个是EXTPROC_CONNECTION_DATA,用来映射extproc代理.
标准的tnsnames.ora格式如下:
CODE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = <host_name>.<domain_name>)
(PORT = 1521)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = <database_sid>)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)
(KEY = EXTPROC)
)
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

tnsnames.ora服务名提供了一个使用户和程序连接到监听的连接别名.
通过tnsnames.ora中的ADDRESS参数来解释连接请求,然后使用CONNECTION_DATA参数来连接到数据库或代理.
extproc代理不仅仅是oracle 11g支持的唯一代理.
你可以定义任意数量的可在oracle和其他数据库之间通信的异构服务.
可通过TNSPING 工具来测试extproc是否正常解释.
如果正常,你使用TNSPING得到的结果可能如下:
TNS Ping Utility for Linux: Version 11.1.0.6.0 - Production on 22-AUG-2007
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
/u03/oracle/11g/11.1.0/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =
IPC)(KEY = EXTPROC1521))) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO)))
OK (0 msec)

配置支持extproc代理的监听:
服务器监听文件listener.ora配置

LISTENER =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))
)
)

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (SID_NAME=cry)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
    )
  )

CALLOUT_LISTENER=
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=IPC)(KEY=extproc))
  )

SID_LIST_CALLOUT_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (SID_NAME=PLSExtProc)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM=extproc)
      (ENV="EXTPROC_DLLS=ONLY:
      /u01/app/oracle/product/11.2.0/dbhome_1/customlib/writestr1.so,
      LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/dbhome_1/lib")
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

服务器文件tnsnames.ora配置

cry=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cry)
    )
  )

EXTPROC_CONNECTION_DATA=
  (DESCRIPTION=
   (ADDRESS=(PROTOCOL=IPC)(KEY=extproc))
  (CONNECT_DATA=
    (SID=PLSExtProc)
    (PRESENTATION=RO)
  )
 )

然后执行以下命令(LINUX平台)进行验证:
[oracle@oracleserver ~]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 05-JAN-2013 04:15:05

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
The command completed successfully
[oracle@oracleserver ~]$ lsnrctl start LISTENER

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 05-JAN-2013 04:15:20

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oracleserver/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 05-JAN-2013 04:15:20
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oracleserver/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@oracleserver ~]$ lsnrctl start CALLOUT_LISTENER

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 05-JAN-2013 04:15:30

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oracleserver/callout_listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=extproc)))
STATUS of the LISTENER
------------------------
Alias CALLOUT_LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 05-JAN-2013 04:15:30
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oracleserver/callout_listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@oracleserver ~]$ lsnrctl status listener

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 05-JAN-2013 04:39:43

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 05-JAN-2013 04:35:08
Uptime 0 days 0 hr. 4 min. 35 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oracleserver/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Services Summary...
Service "cry" has 1 instance(s).
Instance "cry", status READY, has 1 handler(s) for this service...
Service "cryXDB" has 1 instance(s).
Instance "cry", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@oracleserver ~]$
[oracle@oracleserver ~]$ lsnrctl status CALLOUT_LISTENER

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 05-JAN-2013 04:40:11

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=extproc)))
STATUS of the LISTENER
------------------------
Alias CALLOUT_LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 05-JAN-2013 04:15:30
Uptime 0 days 0 hr. 24 min. 40 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oracleserver/callout_listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@oracleserver ~]$ tnsping EXTPROC_CONNECTION_DATA

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 05-JAN-2013 04:50:37

Copyright (c) 1997, 2009, Oracle. All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=IPC)(KEY=extproc)) (CONNECT_DATA= (SID=PLSExtProc) (PRESENTATION=RO)))
OK (10 msec)

由上可知,我们已经配置了2个监听服务(CALLOUT_LISTENER和listener),并已成功启动.
其中CALLOUT_LISTENER是用来监听extproc代理的.
如果此时您使用该连接名登录,将会获得ORA-28547错误,如下:
[oracle@oracleserver ~]$ sqlplus scott/tiger@EXTPROC_CONNECTION_DATA

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 5 04:52:59 2013

Copyright (c) 1982, 2009, Oracle. All rights reserved.

ERROR:
ORA-28547: connection to server failed, probable Oracle Net admin error

这个错误是正常的,说明连接已被extproc代理拒绝.

3.定义多线程的外部存储代理
配置异构的多线程代理是一个复杂的过程.
不过它可以让你在多个数据库会话间共享单个的extproc代理,这样可以减少资源使用.
外部存储缺省情况下每个调用将fork一个新的extproc代理.这个缺省的行为将会频繁地消耗很多资源.
当你有很多使用外部存储的会话时,你应该使用一个多线程的extproc代理.
在配置多线程的外部存储代理前,这里有3点应当注意:
1.外部库必须是线程安全的.
2.代理进程、数据库服务器、监听进程必须在同一台主机
3.代理进程和外部存储调用必须运行在同一个数据库实例上.

使用agtctl配置多线程extproc代理
agtctl是一个代理控制工具,用来启动和管理会话.它在$ORACLE_HOME/bin目录下.
agtctl没有GUI界面,它要么运行在单行命令模式,要么运行在agtctl shell模式.
agtctl有8个命令:
1.delete 作用:删除一个agent_sid实体
2.exit 作用:推出agtctl
3.help 作用:显示可用的命令
4.set 作用:设置参数值
5.show 作用:显示参数值
6.shutdown 作用:关闭一个agent_sid多线程代理
7.startup 作用:启动一个agent_sid多线程代理
8.unset 作用:取消一个参数值的设置
相关的初始化参数值:
参数:listener_address
缺省值:
(ADDRESS_LIST =
(ADDRESS =(PROTOCOL = IPC)(KEY = PNPKEY))
(ADDRESS =(PROTOCOL = IPC)(KEY= <oracle_sid>)
(ADDRESS =(PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)))
参数:max_dispatchers
缺省值:1
参数:max_sessions
缺省值:5
参数:max_task_threads
缺省值:2
参数:shutdown_address
缺省值:
(ADDRESS_LIST =
(ADDRESS =(PROTOCOL = IPC)(KEY = extproc)))
tcp_dispatchers
缺省值:0

下面将演示使用agtctl工具配置extproc多线程代理(需要按上面配置好extproc监听).
--启用50个会话和2个调度器

[oracle@oracleserver admin]$ agtctl

AGTCTL: Release 11.2.0.1.0 - Production on Sat Jan 5 05:29:51 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

AGTCTL> set agent_sid CALLOUT_LISTENER
AGTCTL> set max_dispatchers 2
AGTCTL> set max_sessions 50
AGTCTL> show max_sessions
50
AGTCTL> show max_dispatchers
2
AGTCTL> startup extproc
AGTCTL> exit

此时发现多线程外部存储代理已经启动,如下:

[oracle@oracleserver admin]$ ps -ef|grep -v grep|grep extprocCALLOUT
oracle    6612     1  0 05:31 ?        00:00:00 extprocCALLOUT_LISTENER -mt

和关闭数据库一样,使用shutdown immediate命令来关闭多线程外部存储代理.
当你开启了extproc多线程代理,所有新的外部存储调用将通过多线程代理来调用.
但是之前的调用还是使用动态的单个extproc代理,直到调用完成.