>
Unfortunately, explicitly declaring TABLE OFs for each column returned is what's needed when using the MS ODBC driver technique. I had put this away for when I have some time and I can create a builder for this (I already created one for CUD SPs (Insert, Update, Delete, no Read)) because4 of the amount of code involved.
>

Interestingly, I ran into MS KB article #Q255043 that shows how to get a ref cursor back into an ADO recordset and the exact thing we are trying to do works fine there. I translated the VB code to VFP and viola! Of course, I don't want it in ADO, but it's interesting that it does work that way.

The other difference here, of course, is that the OLE DB provider is being used instead of the ODBC driver. I suppose we can't connect to Oracle via OLE DB without using ADO? Would be nice to somehow use the OLE DB provider and see if we could get the result set back, but as a VFP cursor. Oh well.

Here's the converted VB code from the KB article, in case anyone is interested:

#DEFINE CONNECT_STRING "Provider=MSDAORA.1;Data Source=MyServer;User ID=MyUser;Password=MyPwd"
		
	LOCAL cn, rs, cmd, param, SQL

	cn = CreateObject( "ADODB.Connection" )
	cn.Open ( CONNECT_STRING )

	cmd = CreateObject ("ADODB.Command")

*!* It works this way, with a parameter object and also works the 2nd way, 
*!* with the parameter embedded within the command.

*!*		SQL = "{call TestRefCursor.GetData(?, {resultset 0, io_cursor})}"

*!*		with cmd
*!*			.ActiveConnection	= cn
*!*		    .CommandText			= SQL
*!*		    .CommandType			= 1		&& adCmdText
*!*		    param = .CreateParameter("n_EmpNo", 131 , 1 , , 2)  &&  131=adNumeric, 1=adParamInput
*!*		    .Parameters.Append ( param )
*!*		endwith

	* Note that passing "resultset 0" gets all records.
	SQL = "{call TestRefCursor.GetData(2, {resultset 0, io_cursor})}"

	with cmd
		.ActiveConnection	= cn
	    .CommandText		= SQL
	    .CommandType		= 1		&& adCmdText
	endwith

	rs = cmd.execute

	IF VARTYPE ( rs ) = "O"
		WITH rs
			IF NOT .Eof
				FOR EACH oField IN rs.Fields
					? oField.Name, oField.Value
				NEXT oField
			ENDIF
		ENDWITH
	ENDIF
	
	STORE .F. TO rs, cmd, cn, param

The Oracle package for TestRefCursor, a simplfied version of what's in the KB article, looks like this:

CREATE OR REPLACE PACKAGE testrefcursor AS

   TYPE t_cursor IS REF CURSOR ;

   Procedure GetData (n_PK IN NUMBER, io_cursor IN OUT t_cursor);

END;
/

CREATE OR REPLACE PACKAGE BODY testrefcursor AS

Procedure GetData (n_PK IN NUMBER, io_cursor IN OUT t_cursor) IS

   v_cursor t_cursor;

   BEGIN

        OPEN v_cursor FOR
        SELECT *
          FROM MyTable
         WHERE pk = n_PK;

     io_cursor := v_cursor;

   END;
END;
/