I have a form in MS Access/vb and I am trying to connect to Oracle Database and call a ref cursor in Oracle PL/SQL procedure using ADO.

I am able to connect successfully, but when I execute the command (when I call the ref cursor) I get a RUNTIME UNSPECIFIED ERROR.

In my procedure p_user_id is an input numeric variable and then there is a ref cursor.

Following is my code (please help me determine what am I missing):


Option Explicit

Public m_adoCnn As New ADODB.Connection
Public m_adoRst As New ADODB.Recordset


Private Sub Command0_Click()


Dim Cn As ADODB.Connection
Dim CP As ADODB.Command
Dim Rs As ADODB.Recordset
Dim Conn As String
Dim SSQL As String
Dim Pr As New ADODB.Parameter

Set CP = New ADODB.Command
Set Cn = New ADODB.Connection

'Connect to Oracele server begin
Conn = "Provider=OraOLEDB.Oracle;Data Source=x;UserID=y; Password=z;PLSQLRset=1;"


With Cn
.ConnectionString = Conn
.CursorLocation = adUseClient
.Open
End With

If Cn.State = adStateOpen Then
MsgBox "Connection successful."
End If

SSQL = "{call test_pkg.test_PROC(?)}"

With CP
.ActiveConnection = Cn
.CommandType = adCmdStoredProc
.CommandText = SSQL

.Parameters.Append .CreateParameter("p_user_id", adNumeric, adParamInput, , 5)

Set Rs = .Execute()

End With


'Connect to Oracle server end


'close connection begin
Cn.Close
Set Cn = Nothing
Set CP = Nothing
'close connection end


End Sub

Private Sub Form_Unload(Cancel As Integer)
m_adoCnn.Close
End Sub