| runtime error while trying to call a PL/SQL refcursor from VB using ADO [message #163881] |
Mon, 20 March 2006 09:20  |
mmk1212
Messages: 21
Registered: January 2006
|
Junior Member |
|
|
I don't know where else to post this thread so I am posting it here:
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
