Calling Oracle Function to Return Cursor

  • Thread starter Thread starter SeamusMcgee
  • Start date Start date
S

SeamusMcgee

Guest
Hello, I am trying to call a oracle function which returns a cursor which i then open as a recordset in vb.

CREATE OR REPLACE FUNCTION MYSCHEMA.get_allitems
RETURN SYS_REFCURSOR
AS
my_cursor SYS_REFCURSOR;
BEGIN
OPEN my_cursor FOR SELECT * FROM MYSCHEMA.MYTABLE;
RETURN my_cursor;
END get_allitems;

Im having an issue with the code below retrieving the cursor.

Sub cursor_test()
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim conn As ADODB.Connection

Set rs = CreateObject("ADODB.Recordset")
Set conn = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
conn.ConnectionString = STRCON
conn.CursorLocation = adUseClient
conn.Open

With cmd
.cmd.CommandTimeout = 60
.ActiveConnection = conn
.CommandType = adCmdStoredProc
.CommandText = "myschema.get_allitems"
.Parameters.Append .CreateParameter(, adNumeric, adParamOutput)
Set rs = .Execute
End With

conn.Close
Set strcmd = Nothing
Set rs = Nothing
End Sub


The error occurs when i execute the function. runtime error 2147217887.

Thanks for any help you can assist me with.

Continue reading...
 
Back
Top