Forum Stats

  • 3,734,446 Users
  • 2,246,973 Discussions
  • 7,857,293 Comments

Discussions

How to deploy .Net stored procedures ?

Youn
Youn Member Posts: 95 Blue Ribbon
edited May 24, 2021 6:14PM in .NET Stored Procedures

Hi community,

I have the following procedure written in .Net I'm trying to deploy to Oracle DB 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.101.34.51)(PORT = 1521))      
	  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
	  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )      	  
  )

I created OraClrAgnt using the 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; 

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.

Tagged:

Answers

Sign In or Register to comment.