How to solve ORA-02019: connection description for remote database not found?

Viewed 2033

I have the following procedure written in .Net that I'm trying to deploy to Oracle 18c

Public Class Class1
    Public Shared Function StoredProcedure1() As String
        Return "hi"
    End Function
End Class

I used Deploy feature on Visual Studio and the deployment was successful.

I configured TNSNames.ora as follows :

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

LISTENER_XE =
 (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

ORACLR_CONNECTION_DATA =
 (DESCRIPTION =
  (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  )
  (CONNECT_DATA =
   (SID = CLRExtProc)
   (PRESENTATION = RO)
  )
 )
EXTPROC_CONNECTION_DATA =
 (DESCRIPTION =
  (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  )
  (CONNECT_DATA =
   (SID = PLSExtProc)
   (PRESENTATION = RO)
  )
 ) 

and Listener.ora as follows :

DEFAULT_SERVICE_LISTENER = XE

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
       (SID_NAME = PLSExtProc)
       (ORACLE_HOME = C:\app\Administrator\product\18.0.0\dbhomeXE)
       (PROGRAM = extproc)      
    )
   (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\Administrator\product\18.0.0\dbhomeXE)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\18.0.0\dbhomeXE\bin\oraclr18.dll")
    )   
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.91.87.51)(PORT = 1521))      
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )         
  )

I created OraClrAgnt with default values :

oraclrctl.exe -new

The service was created successfully and running.

TNSNames.ora seems to be configured properly since when I check it using TNSPing the entries are resolved :

tnsping ORACLR_CONNECTION_DATA
tnsping EXTPROC_CONNECTION_DATA

However when using the procedure in SQL I'm getting the error : connection description for remote database not found.

select STOREDPROCEDURE1 from dual; 

Error :

ORA-02019: connection description for remote database not found
ORA-06512: at "SYS.DBMS_CLR", ligne 243
ORA-06512: at "SYS.DBMS_CLR", ligne 42
ORA-06512: at "SYS.DBMS_CLR", ligne 236
ORA-06512: at "SCHEMA.STOREDPROCEDURE1", ligne 6
02019. 00000 - "connection description for remote database not found"

Does anyone know how to solve that please ?

Thanks

Here is the output of the command lsnrctl services

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.91.87.51)(PORT=1521)))
    Services Summary...
    Service "CLRExtProc" has 1 instance(s).
      Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
        Handler(s):
          "DEDICATED" established:0 refused:0
             LOCAL SERVER
    Service "PLSExtProc" has 1 instance(s).
      Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
        Handler(s):
          "DEDICATED" established:0 refused:0
             LOCAL SERVER
    Service "XE" has 1 instance(s).
      Instance "xe", status READY, has 1 handler(s) for this service...
        Handler(s):
          "DEDICATED" established:469 refused:0 state:ready
             LOCAL SERVER
    Service "XEXDB" has 1 instance(s).
      Instance "xe", status READY, has 1 handler(s) for this service...
        Handler(s):
          "D000" established:0 refused:0 current:0 max:1022 state:ready
             DISPATCHER <machine: SVD5FD3, pid: 4564>
             (ADDRESS=(PROTOCOL=tcp)(HOST=SVD5FD3)(PORT=49725))
    The command completed successfully
0 Answers
Related