calling store procedure in oracle with ADO

I want to get a recoreset from SP in oracle.

the code of store precedure is listed as below:
---------------------------------------------
CREATE OR REPLACE PROCEDURE SP_TEST
(
ret_cur out DEFPKG.EMPTY_CUR
)
IS
BEGIN
OPEN RET_CUR FOR Select * FROM mytable;
END SP_TEST;
-------------------------------------------------
the type "DEFPKG.EMPTY_CUR" is defined in package "DEFPKG"
TYPE EMPTY_CUR IS REF CURSOR;

And the calling codes is:
---------------------------------------------------------
.....
QSQL = "{call SP_TEST({resultset 1000,ret_cur})}"

Set CPw = New ADODB.Command
With CPw
Set .ActiveConnection = Cn
.CommandText = QSQL
.CommandType = adCmdText
End With

Set Rs = New ADODB.Recordset
With Rs
.CursorType = adOpenStatic
.LockType = adLockReadOnly
End With

Rs = CPw.Execute 'Error occurs
--------------------------------------

when running to the last line,system report ORA-01060 error.
(array binds or executes not allowed).
Oracle document tell me that this is because "The client application attempted to bind an array of cursors or attempted to repeatedly execute against a PL/SQL block with a bind variable of type cursor".
But I donn't know how to solve it.

Thanks a lot.

...it may be so..


set Rs = CPw.Execute 'error occurs




hi,brt


<center>
<HR width=80%>
<img src='http://web.tiscali.it/bertaplanet/im...ertaplanet.gif'>
</center>

This may help: you can find a full example in Msdn:

HOWTO: Retrieve Recordsets From Oracle Stored Procs Using ADO

The information in this article applies to:
 Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0, 6.0
 Microsoft ODBC for Oracle (2.73.7283.03), version 2.0
 Microsoft ODBC for Oracle (2.573.2927), version 2.5
 Microsoft Data Access Components versions 2.0, 2.1 SP2, 2.5

SUMMARY
This article shows how to create a Visual Basic 5.0 and ActiveX Data Objects (ADO) 1.5 project or a Visual Basic 6.0 and ADO 2.0 project that returns a typical Recordset from an Oracle stored procedure. This article builds on the concepts covered in the following Microsoft Knowledge Base article:
Q174679 HOWTO: Retrieve Typical Resultsets From Oracle Stored Procedures
It is almost identical to the following article in the Microsoft Knowledge Base article that covers the topic using Remote Data Objects (RDO):
Q174981 HOWTO: Retrieve Typical Resultsets From Oracle Stored Procedures
MORE INFORMATION
The following Knowledge Base article gives an in-depth example, using RDO, of all the possible ways to return a Recordset back from a stored procedure. The example in this article is a simplified version:
Q174679 HOWTO: Retrieve Resultsets from Oracle Stored Procedures
NOTE: The Recordsets created by the Microsoft ODBC Driver for Oracle versions 2.0 and 2.5, using Oracle stored procedures, are Read Only and Static. Retrieving a Recordset requires you to create an Oracle Package.

You can create the sample project in this article in Visual Basic 5.0 or 6.0 and use ADO to access and manipulate the Recordsets created by the Microsoft ODBC Driver for Oracle version 2.0 or 2.5. You must have this driver to use the recordsets-from-stored-procedures functionality discussed in this Knowledge Base article:
Q174679 HOWTO: Retrieve Resultsets from Oracle Stored Procedures
(Currently, it is the only driver on the market that can return a Recordset from a stored procedure.) If you want additional information about using Visual Basic with Oracle, please see the following Knowledge Base article, which uses RDO 2.0 in its examples:
Q167225 HOWTO: Access an Oracle Database Using RDO
NOTE: You will need to acquire and install the MDAC 1.5 or 2.0 stack for the sample in this article. The following Microsoft Knowledge Base article explains how to get the Oracle and MDAC components:
Q175018 HOWTO: Acquire and Install the Microsoft Oracle ODBC Driver
MDAC 1.5 contains ADO 1.5 and the Microsoft ODBC Driver for Oracle version 2.0.

The MDAC 2.0 stack, which includes the 2.5 driver, can be downloaded from the following Web address:
http://www.microsoft.com/data/
This article is broken up into two parts. The first part is a step-by-step procedure for creating the project. The second part is a detailed discussion about the interesting parts of the project.
Step-by-Step Example
Run the following DDL script on your Oracle server:
(you will find the complete example in MSDN)


Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, TCartwright, Bruno Paris, Dr_Micahel
and all the other wonderful people who made and make Codeguru a great place.
Come back soon, you Gurus.

The Rater