Returning Recordset to VB from Oracle Package Function 
Author Message
 Returning Recordset to VB from Oracle Package Function

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. 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

--
Regards,

Ed Holloman



Fri, 16 Apr 2004 09:04:16 GMT  
 Returning Recordset to VB from Oracle Package Function
Hi,

If you want to execute SP with adCmdStoredProc option then
probably you will need to remove Call from sSQL

Use sSQL="test.mycur"

Val

Quote:
>-----Original Message-----
>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. 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

>--
>Regards,

>Ed Holloman
>.



Fri, 16 Apr 2004 23:53:27 GMT  
 Returning Recordset to VB from Oracle Package Function
Check this article out and see if it doesn't shed some light on the problem:

Q239771: HOWTO: Return Oracle Stored Procedure Resultset to ADO Recordset
http://support.microsoft.com/support/kb/Articles/q239/7/71.asp

Hope this helps

Steven Bras, MCSD
Microsoft Developer Support/Visual Basic WebData

This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use. ? 2001 Microsoft Corporation. All rights
reserved.



Mon, 19 Apr 2004 02:39:29 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. call package function in Oracle from VB....

2. Return recordset from Oracle Stored Procedure to VB.

3. Dr. Watson error returning a resultset from an Oracle package

4. Dr. Watson error returning a resultset from an Oracle package

5. How to call FUNCTION in Oracle package?

6. Returning a recordset from oracle using ado

7. Returning Recordsets with ADO from Oracle stored proc

8. Returning Recordset With Oracle Stored Procedure?

9. Return Recordset from Oracle using ADO

10. Oracle recordset opened but no records returned

11. Recordset always return with upper case field names from Oracle

12. Oracle Stored Procedure returning multiple recordsets.

 

 
Powered by phpBB® Forum Software