Home » Infrastructure » Windows » runtime error while trying to call a PL/SQL refcursor from VB using ADO
runtime error while trying to call a PL/SQL refcursor from VB using ADO [message #163881] Mon, 20 March 2006 09:20 Go to next message
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

Re: runtime error while trying to call a PL/SQL refcursor from VB using ADO [message #164249 is a reply to message #163881] Wed, 22 March 2006 09:34 Go to previous messageGo to next message
mmk1212
Messages: 21
Registered: January 2006
Junior Member
Seems like if I do not have any input parameters in my package and I call that package from VB, it works.

How to get it working while also passing input variables?

Anyone?
Re: runtime error while trying to call a PL/SQL refcursor from VB using ADO [message #164348 is a reply to message #164249] Thu, 23 March 2006 01:34 Go to previous message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Moved to "Windows and Dos".

MHE
Previous Topic: Installation of Oracle 10g on Windows 2003 Server
Next Topic: ORA-12560: TNS: protocol adapter error
Goto Forum:
  


Current Time: Thu Mar 28 20:40:44 CDT 2024