oracle 中调用DLL

      前段时间由于业务需要，需要在oracle中调用一个外部标准的dll（libPassChk.dll），但是这个dll的返回值为longbool类型的，oracle没有相应的数据类型与之对应，由于这个dll没有源码，因此只好自己再写一个dll（test.dll）去调用哪个标准的dll，把返回类型修改成Pchar（oracle中用string才能正常匹配），

test.dll的源码如下：

 

library test;

uses
  SysUtils,
  Classes;
  function IsValidPassword(APass: PChar; APassEncoded: PChar): LongBool; stdcall; external 'libPassChk.dll';
{$R *.res}

function  usertest(APass: PChar; APassEncoded: PChar):PChar;stdcall;
begin
  if   IsValidPassword(APass,APassEncoded) then
    Result:='1'
  else
    Result:='0';
end;

exports
   usertest;

begin
end.

 

把上述2个dll，拷贝到%oracle_home%/bin 目录下。

在ORACLE 数据库中增加一个library 名字为 ：ORACLEDLLTEST。

创建脚本如下：

CREATE OR REPLACE LIBRARY ORACLEDLLTEST
 IS 'G:/oracle/product/10.2.0/db_1/BIN/test.dll'

 

在ORACLE 数据库中增加一个function 名字为ORACLE。

创建脚本如下:（注意参数的数据类型）

CREATE OR REPLACE FUNCTION PORTMIS_OA.ORACLE(a varchar,b varchar) RETURN varchar AS
  LANGUAGE C NAME "usertest"
  LIBRARY ORACLEDLLTEST
  PARAMETERS (a string,b string,
               RETURN string );

 

我们可以进行测试：

 

select('tx','123') from dual;

 

在运行过程中遇到 ORA-28575: 无法打开与外部过程代理程序的 RPC 连接  这个错误提示，

这个问题就是oracle不能正常调用外部模块，可以通过tnsping EXTPROC_CONNECTION_DATA  来测试oracle是否能正常调用外部模块，如果能正常ping通的话，就不会出现上面的错误提示了。

 

如果有metalin上面查找Note 70638.1  Title: External Procedures - Troubleshooting ORA-28575 Errors找到相应的解决方案

地址为：https://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=70638.1&blackframe=1

 

我把里面的内容摘录如下：

 

Applies to:

PL/SQL - Version: 7.3.4.5
Generic UNIX
Checked for relevance on 30-Jun-2009

Purpose

This article provides a brief overview of the Oracle external procedure (external subprogram) architecture and provides a number of suggestions for troubleshooting the ORA-28575 error. Although the emphasis of this bulletin is on the UNIX environment, many of the concepts also apply to Windows and other environments.

This article does not address all aspects of external procedures. It provides just enough background information to provide a context for discussing the ORA-28575 error which is the focus of this article.

Scope and Application

A list of the essential components for executing an external procedure is included below, along with a brief description of the role of each.

Database:

• Responsible for storing any information required to locate and execute an external procedure (e.g. the location of shared library, number and types of arguments, etc.)
• Initiates the call to the procedure via the listener and external procedure agent
• Both the library alias and call specification are stored in the database.

Llistener:

• Listens for external procedure requests from databases and starts external procedure agent processes (one per session)

External procedure agent:

• Accepts information (arguments, shared library location, etc.) from the database, loads the shared library as required, and executes the corresponding function in some other language
• Corresponds to the extproc executable which resides in $ORACLE_HOME/bin

User-provided shared library:

• Contains the user-written function that implements the desired functionality