ORA-28575 Executing a .NET Assembly Using the Oracle Database Extensions for .NET (ODE.NET) (Doc ID 1316540.1)

Last updated on AUGUST 25, 2017

Applies to:

Oracle Database Extensions for .NET - Version 10.2.0.1 and later
Microsoft Windows (32-bit)
***Checked for relevance on 03-Apr-2013***

Symptoms

You are attempting to run a .NET assembly which has been deployed to the Oracle Database.  The assembly uses the Oracle Database Extensions for .NET (ODE.NET) feature where a PL/SQL stored procedure/function is created that makes an external procedure call to an Oracle DLL that will call the .NET assembly and return any results.  You execute the PL/SQL stored procedure/function and receive the following error from Visual Studio:

Run Function - SCOTT.GETDEPTNO@SYS.ORCL

ORA-28575: unable to open RPC connection to external procedure agent
ORA-06512: at "SYS.DBMS_CLR", line 152
ORA-06512: at "SCOTT.GETDEPTNO", line 7
ORA-06512: at line 1



Changes

You have modified your LISTENER.ORA and TNSNAMES.ORA files which look similar to the following examples...

LISTENER.ORA

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )

  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = database.oracle.com)(PORT = 1521))

    )
  )


TNSNAMES.ORA

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = database.oracle.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.us.oracle.com)
    )
  )

  ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )


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

 

NOTE:  Be sure the name/value pair, (KEY = EXTPROC1), in your LISTENER.ORA file is the same name/value pair, (KEY = EXTPROC1), used in the ORACLR_CONNECTION_DATA entry in your TNSNAMES.ORA file.



After modifying the above files...

  1. You stopped the database.

  2. You restarted the listener.

  3. You started the database.

  4. Checked the status of the listener which shows the CLRExtProc successfully registered:

    C:\>lsnrctl status

    LSNRCTL for 32-bit Windows: Version 10.2.0.4.0 - Production on 25-APR-2011 17:59:03

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

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for 32-bit Windows: Version 10.2.0.4.0 - Production
    Start Date 25-APR-2011 17:54:09
    Uptime 0 days 0 hr. 4 min. 56 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File D:\oracle\product\10.2.0\db_1\network\admin\listener.ora
    Listener Log File D:\oracle\product\10.2.0\db_1\network\log\listener.log

    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myhostname)(PORT=1521)))
    Services Summary...
    Service "CLRExtProc" has 1 instance(s).
    Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...

    Service "PLSExtProc" has 1 instance(s).
    Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Service "orcl" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
    Service "orclXDB" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
    Service "orcl_XPT" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
    The command completed successfully
  5. TNSPING the entry ORACLR_CONNECTION_DATA and receive an error:

    C:\>tnsping ORACLR_CONNECTION_DATA

    TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 25-APR-2011 17:59:54

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

    Used parameter files:
    D:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora

    TNS-03505: Failed to resolve name
  6. The service Oracle<OracleHomeName>ClrAgent (ex: OracleOraDb11g_home1ClrAgent) is running and the Status of the service displays Started.

  7. The .NET assembly has been deployed to the Oracle Database and can be found in the following location:

         ORACLE_HOME\BIN\CLR  (ex: D:\oracle\product\10.2.0\db_1\BIN\CLR)

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms