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

Library alias:

• Records the location of the shared library within the database
• Used in the PL/SQL call specification

External procedure call specification:

• Provides a description of the procedure, including its PL/SQL name, arguments and types, and the name of the library alias and the function (written in another language) implementing the procedure, or libunit in the case of a Java class method

External Procedures - Troubleshooting ORA-28575 Errors

A Typical Sequence of Events


The following is a typical sequence of events:

1. The user invokes the call specification corresponding to an external procedure via PL/SQL or some other programmatic interface.
   
2. The database examines the call specification to find the name of the shared library implementing the function, as well as the name of the function and the number and type of the arguments.
   
3. The database uses the extproc_connection_data service name in the tnsnames.ora file to contact the external procedure listener.
   
   
   Note: This step only occurs the first time the external procedure is invoked within a given session. After that, the existing external procedure agent can be contacted directly
   
   
   
4. The listener starts an external procedure agent process to service the external procedure execution request and returns the address of the new extproc agent to the requesting database.
   
5. The database sends the external procedure agent the name of the shared library, the name of the function to invoke, and after performing the required conversions from PL/SQL to another language datatypes, the actual arguments.
   
6. The external procedure agent loads the shared library, invokes the appropriate function, converts any returned data from another language datatypes to the corresponding PL/SQL types, and sends the data back to the database.

Why does the ORA-28575 error occur?

The ORA-28575 error indicates that a failure occurred in steps 3-4 above. Either the database was unable to establish a connection to the external procedure listener (step 3), OR the external procedure
listener was unable to execute the extproc agent (step 4).


Troubleshooting the ORA-28575 error

Reasons for the ORA-28575 include:

• Issues with extproc
• Database configuration issues
• Listener configuration issues

Issues with extproc

You can receive this error if the extproc executable either does not exist, OR the listener does not have permissions to execute it, OR you cannot execute extproc for any other reason.

Check the following:

1. Verify that the extproc executable specified in listener.ora exists in the bin directory of the specified $ORACLE_HOME. See the section on listener configuration below for more information.
   
2. Verify that extproc can be executed by the listener. On many systems, the listener executes with the effective user id of the oracle user, but this can be changed. Verify that the permissions are available for whatever user is applicable.
   
3. Other problems such as file corruption could also cause the extproc program to not be executable. Perform the following steps to verify whether the extproc program is valid and executable:
   
   
   • Log in to the system as the applicable user.
   • cd to the $ORACLE_HOME/bin directory.
   • Execute the following command at the prompt:
     
     
     $ ./extproc 
     
     
     If all is well, you should see a banner displayed similar to the following (the text may differ slightly):
     
     
     Oracle Corporation --- MONDAY SEP 24 2007 12:32:26.903

Heterogeneous Agent Release 9.2.0.8.0 - Production

     
     or
     
     
     Oracle Corporation --- MONDAY SEP 24 2007 12:31:27.351

Heterogeneous Agent Release 10.2.0.3.0 - Production
     
     
     If you do not see such a banner or you receive an error, you may need to reinstall and/or rebuild the extproc executable or change the permissions to make it executable.
     

Database Configuration Issues

This article does not address database configuration issues other than those which result in the ORA-28575 error.

Database configuration issues resulting in an ORA-28575 include:

1. The database is referencing the wrong copy of tnsnames.ora
   
   In some environments, multiple copies of the tnsnames.ora file may exist in the file system. The copy used by the database is that which is pointed to by TNS_ADMIN when the database is started.
   
   To ensure that your changes to the tnsnames.ora file are picked up by the database, you may need to shut down the database, set the TNS_ADMIN environment variable to indicate the directory with
   the appropriate file, and restart the database.
   
2. The tnsnames.ora file is improperly configured
   
   The tnsnames.ora file must be configured to contain a special service name called extproc_connection_data. This service name provides the database with the necessary parameters to connect to the external procedure listener. The entry looks like:
   
   
   EXTPROC_CONNECTION_DATA=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc_key))
(CONNECT_DATA=
(SID=extproc_agent))) 
   
   
   • extproc_connection_data cannot be changed except as follows:
     
     If, and only if, the sqlnet.ora file contains the following:
     
     names.default_domain = some.domain (e.g. us.oracle.com)
     
     then you must modify the above tnsnames.ora entry as follows:
     
     
     EXTPROC_CONNECTION_DATA.SOME.DOMAIN=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc_key))
(CONNECT_DATA=
(SID=extproc_agent))) 
     
     Setting default_domain causes some.domain to be automatically appended to all service names that are not already qualified with a domain. This is also true for extproc_connection_data.
     
     If default_domain is set, and that domain is not appended to the service name in tnsnames.ora, the connection fails.
     
     Note: As was the case for tnsnames.ora, the database uses the sqlnet.ora file that was pointed to by TNS_ADMIN at the time the database was last started. Make sure you are looking at the right copy of the file.
     
   • extproc_key must be the same as the KEY=extproc_key entry in the listener.ora file (see below). Otherwise, it can be any string that is not already in use. This uniquely identifies
     which external procedure listener is to be contacted.
     
   • extproc_agent must be the same as the SID_NAME=extproc_agent entry in the listener.ora file (see below). Otherwise, it can be any SID that is not already in use. This is the service that appears in the list displayed by 'lsnrctl status'.
     
   • PROTOCOL must be set to IPC. Other protocols (e.g. TCP) are currently not supported for external procedures.
     
3. The names.directory_path parameter in the sqlnet.ora file does not include the tnsnames.ora file as a source of directory info.
   
   
   If the sqlnet.ora file pointed to the the TNS_ADMIN environment variable contains an entry of the following form:
   
   
   names.directory_path = (tnsnames, onames)
   
   
   then Net looks in both the tnsnames.ora file and Oracle Names to resolve service names (including extproc_connection_data). If the 'tnsnames' entry is omitted as follows:
   
   
   names.directory_path = (onames)
   
   
   then only Oracle Names is searched and the modifications to the tnsnames.ora file are not picked up by the database.
   
   

Listener Configuration Issues

This bulletin does not address listener configuration issues other than those which result in the ORA-28575 error.

Listener configuration issues resulting in an ORA-28575 include:

1. The listener was started with the wrong copy of listener.ora
   
   The external procedure listener reads the listener.ora pointed to by TNS_ADMIN when it is started. If the listener is started prior to some modification to the listener.ora file, you must shut down the listener, set the TNS_ADMIN environment variable to point to the modified copy of listener.ora, and restart the listener.
   
   For example:
   
   
   $ lsnrctl stop callout_listener
$ export TNS_ADMIN=/u02/etc # assumes Korn shell
$ lsnrctl start callout_listener 
   
   The name callout_listener is not fixed, but is used in this example for consistency with the examples that follow.
   
2. The listener.ora file is improperly configured
   
   When configuring the listener for external procedures, the following two options exist:
   
   
   • Configure the existing database listener to listen for external procedures and database connections
     
     or
     
   • Configure a separate listener specifically for external procedures. 
   
   There are several benefits to the second option including:
   
   
   • Maintenance is simplified since the external procedure listener can be started and stopped independent of database connections.
     
   • Security is enhanced since a separate listener can be run with a different effective user id. Running external procedures as the 'oracle' (or equivalent) user can expose the database to unnecessary risk since user-written procedures can then have unrestricted access to database resources (e.g. datafiles).
     
   For these reasons, we recommend the second method. It is the only method addressed in this article.
   
   To properly configure a separate listener for external procedures:
   
   
   • Decide on a name for the new listener (callout_listener in the examples that follow). This name appears in several places in the listener.ora file. Substitute the name you selected for callout_listener in the examples that follow.
     
   • Modify the listener.ora file as follows:
     
     
     • Add the following entries:
       
       
       callout_listener =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = IPC)
(KEY = extproc_key)
)
)

sid_list_callout_listener =
(SID_LIST =
(SID_DESC =
(SID_NAME = extproc_agent)
(ORACLE_HOME = oraclehomedir)
(PROGRAM = extproc)
)
) 
       
       where callout_listener is the name of the listener extproc_key agrees with the KEY in tnsnames.ora. extproc_agent agrees with the SID in tnsnames.ora
       
     • Remove any entries related to external procedures from the default listener that is being used to listen for database connections if such entries exist. This is only an issue if you have previously attempted to configure a single listener for both purposes.
       
     • Restart the database listener to make sure it reflects the most recent listener.ora changes. Restarting the original listener is especially important if it was previously set to listen for external procedures.
       
       
       
       lsnrctl stop listener

# C shell syle
# setenv TNS_ADMIN /u02/etc

# other shell
TNS_ADMIN=/u02/etc
export TNS_ADMIN

lsnrctl start listener 
       
       Observe and correct any errors that may have resulted from modifying the listener.ora file (i.e. typographical errors).
       
     • Start the new external procedure listener.
       
       
       lsnrctl start callout_listener 
       
       Observe and correct any errors that may have resulted from modifying the listener.ora file (i.e. typographical errors).
   
   

Testing Your Configuration

The database ships with a sample program which can be used to test your external procedure configuration. You can find the following files in the $ORACLE_HOME/plsql/demo directory:

• extproc.c
  the C file implementing the external procedures
  
• extproc.sql
  an SQL script for creating and invoking the required
  
• packages and procedure/function call specifications.
  
• demo_plsql.mk
  a makefile for compiling the extproc demo

The steps for running the demo are:

1. Compile the extproc.c and create the extproc.so shared library
   
   
   make -f demo_plsql.mk extproc.so 
   
   
2. Ensure user scott and requisite tables (e.g. EMP and DEPT) exist. Also, ensure that the DBMS_OUTPUT package exists and is valid.
   
3. Follow the steps outlined in the comments section of extproc.sql. A couple of steps must be completed before you run the extproc.sql script, including creating the LIBRARY and granting any privileges required.
   When creating the LIBRARY, make sure that you specify the correct location for your shared library. The example assumes that you put it in /tmp, however, this may not be the case.
   
4. From SQL*Plus, execute the following to create the PL/SQL package and call specifications and execute the demo:
   
   
   
   
5. When executed, the demo invokes several examples, each of which produces output to the screen via the DBMS_OUTPUT package. You should not see any errors. If you receive the ORA-28575, retrace the steps in this article to correct the problem. For other errors, refer to the documentation.
   
   
   Note: Under normal conditions the listener is started before the database is started. If the listener has been restarted or started after the database is available, it might be needed to bounce (stop/start) the database to avoid ORA-28575
   

References

• Oracle 9iR1 (9.0.1.X) and 9iR2(9.2.0.X)

Oracle9i Application Developer's Guide - Fundamentals

10 Calling External Procedures

Oracle9i Net Services Administrator's Guide

Configuring Oracle Net Services for External Procedures

PL/SQL User's Guide and Reference

-Calling External Subprograms

• Oracle 10gR1(10.1.0.X) and 10gR2(10.2.0.X)

Oracle® Database Application Developer's Guide - Fundamentals

14 Calling External Procedures

Oracle® Database Net Services Administrator's Guide

Configuring Oracle Net Services for External Procedures

Oracle® Database PL/SQL User's Guide and Reference

Calling External Subprograms