oracle 调用外部exe,oracle调用外部程序时报：ora-28575以及ora-28546错误

今天发现一个有关oracle调用外部程序时报：ora-28575以及ora-28546错误，现将问题的现象，原因和处理办法总结如下：

SQL> create or replace library GENMAILNUM

as '/home/oracle/product/10.2.0/lib/libmailnum.so';  2

/

Library created.

SQL> CREATE OR REPLACE FUNCTION genmailnum_func (

prefix     VARCHAR2,

suffix     VARCHAR2,

seq     VARCHAR2,

mailnum out varchar2)

RETURN PLS_INTEGER

AS LANGUAGE C

LIBRARY genmailnum

NAME "generate_mail_num";

/

2    3    4    5    6    7    8    9   10   11

Function created.

SQL> set serveroutput on

SQL> declare

result  PLS_INTEGER;

prefix  varchar2(20)  :='LN';

suffix  varchar2(20)  :='CN';

seq     varchar2(20)  :='12345678';

mailnum varchar2(20);

begin

-- Call the function

result := genmailnum_func(prefix,

suffix ,

seq ,

mailnum );

end;  2    3    4    5    6    7    8    9   10   11   12   13   14

15  /

ORA-28575: unable to open RPC connection to external procedure agent

而另外一台测试环境上相同的代码，相同的表结构，调用外部程序是可以的。根据此错误，查看监听文件的配置情况：

[oracle@xxxx-test ~]$cat /home/oracle/product/10.2.0/network/admin/listener.ora

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.129)(PORT = 1521))

)

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

)

看来是没有配置静态监听，再来查看监听器状态情况：

[oracle@xxxx-test admin]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 29-DEC-2011 13:25:21

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.129)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production

Start Date                29-DEC-2011 13:23:09

Uptime                    0 days 0 hr. 2 min. 12 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /home/oracle/product/10.2.0/network/admin/listener.ora

Listener Log File         /home/oracle/product/10.2.0/network/log/listener.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.129)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Services Summary...

Service "xxxx" has 1 instance(s).

Instance "xxxx", status READY, has 1 handler(s) for this service...

Service "xxxxXDB" has 1 instance(s).

Instance "xxxx", status READY, has 1 handler(s) for this service...

Service "xxxx_XPT" has 1 instance(s).

Instance "xxxx", status READY, has 1 handler(s) for this service...

Service "cognos" has 1 instance(s).

Instance "cognos", status READY, has 1 handler(s) for this service...

Service "cognosXDB" has 1 instance(s).

Instance "cognos", status READY, has 1 handler(s) for this service...

Service "cognos_XPT" has 1 instance(s).

Instance "cognos", status READY, has 1 handler(s) for this service...

The command completed successfully

通过这个可以看出本数据库服务器的监听都是通过动态监听的。也想尝试添加一个静态的监听，来看看是不是究竟就是静态监听的问题，配置静态监听需要重启监听器。但是这是生产服务器，系统非常的忙，不能重启监听器，否则会给生产带来一定的影响，因此想看看究竟能不能采用动态监听来调用外部程序。

修改tnsnames .ora文件：

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(CONNECT_DATA =

(SID =PLSExtProc)

(PRESENTATION = RO)

)

)

将SID =PLSExtProc改为SID =xxxx

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(CONNECT_DATA =

(SID =xxxx)

(PRESENTATION = RO)

)

)

再来调用外部程序：

SQL> declare

result  PLS_INTEGER;

prefix  varchar2(20)  :='LN';

suffix  varchar2(20)  :='CN';

seq     varchar2(20)  :='12345678';

mailnum varchar2(20);

begin

-- Call the function

result := genmailnum_func(prefix,

suffix ,

seq ,

mailnum );

end;

2    3    4    5    6    7    8    9   10   11   12   13   14   15

16  /

declare

*

ERROR at line 1:

ORA-28546: connection initialization failed, probable Net8 admin error

ORA-06512: at "TMSDEV.GENMAILNUM_FUNC", line 1

ORA-06512: at line 10

此错误一般都是异构数据库透明网关相关的错误，或者是dblink方面的问题，和这个没什么关系，觉得很奇怪。

仔细查过oracle有关网络配置的官方文档：

Static Service Registration (SID_LIST) Section

You can use the SID_LIST section of the listener.ora to statically configure service information for the listener.

The SID_LIST section is required for Oracle8 release 8.0 or Oracle7 database services, as well as external procedure calls and Heterogeneous Services, and some management tools, including Oracle Enterprise Manager.

SID_LIST_listener_name=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=global_database_name)

(SID_NAME=sid)

(ORACLE_HOME=oracle_home))

(SID_DESC=...))

For later database releases, the listener uses the dynamic service information about the database and instance it has received through service registration before using statically configured information in the listener.ora file. Therefore, the SID_LIST is not required, unless Oracle Enterprise Manager is used to monitor an Oracle9i or Oracle8 database.

发现oracle外部程序在10g中必须通过静态监听才能调用。在11g中却没有此要求。

我在另一台10g测试环境上，重新配置了一个静态监听，问题解决：

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.128)(PORT = 1521))

)

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /home/oracle/product/10.2.0)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = xxxx)

(ORACLE_HOME = /home/oracle/product/10.2.0)

(SID_NAME = xxxx)

)

)

重启监听器：

[oracle@xxxx-test admin]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 29-DEC-2011 17:25:54

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.129)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production

Start Date                29-DEC-2011 17:24:21

Uptime                    0 days 0 hr. 1 min. 32 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /home/oracle/product/10.2.0/network/admin/listener.ora

Listener Log File         /home/oracle/product/10.2.0/network/log/listener.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.129)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "xxxx" has 2 instance(s).

Instance "xxxx", status UNKNOWN, has 1 handler(s) for this service...

Instance "xxxx", status READY, has 1 handler(s) for this service...

Service "xxxxXDB" has 1 instance(s).

Instance "xxxx", status READY, has 1 handler(s) for this service...

Service "xxxx_XPT" has 1 instance(s).

Instance "xxxx", status READY, has 1 handler(s) for this service...

Service "cognos" has 1 instance(s).

Instance "cognos", status READY, has 1 handler(s) for this service...

Service "cognosXDB" has 1 instance(s).

Instance "cognos", status READY, has 1 handler(s) for this service...

Service "cognos_XPT" has 1 instance(s).

Instance "cognos", status READY, has 1 handler(s) for this service...

The command completed successfully

静态监听已经有了。

再次调用外部程序：

SQL> declare

result  PLS_INTEGER;

prefix  varchar2(20)  :='LN';

suffix  varchar2(20)  :='CN';

seq     varchar2(20)  :='12345678';

mailnum varchar2(20);

begin

-- Call the function

result := genmailnum_func(prefix,

suffix ,

seq ,

mailnum );

end;

2    3    4    5    6    7    8    9   10   11   12   13   14   15   16

17  /

PL/SQL procedure successfully completed.

问题解决。

另外在11g上也搭建了同样的程序，监听器没有强制配置静态监听，调用外部程序也是可以的。