DevOps, CI/CD and Automation

Hi,

Oracle Client 8.04
Oracle ODBC Driver 8.00.04
VB 6.0
Windows 2000

I'm stumped here. I want to have a Oracle stored procedure run a
query and return a result set which I can assign to a recordset
object in VB. Based on things I've read here and on MS's site,
here's what I've done:

In the Oracle Schema Manager under the Packages folder I created
the following package:
--------------------
PACKAGE test
IS
TYPE test_cur IS REF CURSOR;
FUNCTION mycur RETURN test_cur;
END test;
----------------------
and under the Package Body folder created:
---------------------
PACKAGE BODY test
IS
FUNCTION mycur RETURN test_cur
IS
c_return test_cur;
BEGIN
OPEN c_return FOR
SELECT * FROM table_A;
RETURN c_return;
CLOSE c_return;
END mycur;
END test;
---------------
They both compile without errors and in Oracle SQL Worksheet I
can enter the following:
------------
variable x refcursor;
execute :x :=test.mycur;
print x;
--------------
and the query results are displayed as expected.

The problem is trying to get the result back into a VB recordset
object.

In VB 6.0 I have done this:
---------------------
Dim RS As ADODB.Recordset
Dim Conn As ADODB.Connection
Dim sConnection As String
Dim sSQL As String

sSQL = "{call test.mycur}"
sConnection = "Provider=MSDASQL;UID=" & sUserID & ";PWD=" &
sPassword & ";Driver={Microsoft ODBC for Oracle}; Server=" &
sInstance & ";"
Conn.Open sConnection

RS.CursorLocation = adUseClient
RS.Open sSQL, Conn, adOpenForwardOnly, adLockOptimistic,
adCmdStoredProc ' or adCmdText
------------
but get:
-----------------
?err.Number -2147217900
?err.Source Microsoft OLE DB Provider for ODBC Drivers
?err.Description [Microsoft][ODBC driver for Oracle]Syntax error
or access violation
----------------

The problem is not with the connection or permissions, since the
query works fine when I just use the select statement in the
package function as the string, instead of calling the function
in the package (eg sSQL = "Select * from table_A") and can
process the resulting recordset in VB.

I've also tried variations using:

Set RS = Conn.Execute("{call test.mycur}")

or using a Command object something like:

Dim com As ADODB.Command
Set com = New ADODB.Command

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

With com
.ActiveConnection = Conn
.CommandText = sSQL
.CommandType = adCmdText
End With

Set RS.Source = com
RS.Open

But still get the same errors. Any help is appreciated. Also, in
my package body, is it necessary to explicitly close the cursor,
or does the function just exit when it executes the return and
not ever hit the close statement?

Thanks,

Ed Holloman