Hello,
This is a repost/rewording of a question posted on Friday (with 0 answers).

I have a VFP 6.0 application that runs against a MSSQL server, with no problems.
I need to modify it so it can be used either with MS SQL Server or Oracle.(and keep changes to a minimum)
I have been able to get most of it running with Oracle, with only minor adjustments.

However, I am disappointed with the way Oracle handles stored procedures that return result sets.

Suppose this:

Stored procedure definition in MS-SQL:

create procedure myproc @param char(5)
as
select * from mytable where fieldj = @param
return

This in turn is called in VFP as

sqlexec(myconnection,"execute myproc ?myvar", "result")

And the result set appears nicely in the cursor "result"

As far as I understand, Oracle stored procedures cannot return result sets; the most approximate solution is to make them fill data in a cursor defined in a package (from Oracle Migration Companion)

The definition of the cursor and the procedure (in ORACLE) could be:

CREATE OR REPLACE PACKAGE MYPK AS
TYPE RT1 IS RECORD(
field1 char(12),
field2 number,
.......
fieldn number);
TYPE RCT1 IS REF CURSOR RETURN RT1;
END;

CREATE or replace PROCEDURE myproc (
PARAM1 Char,RC1 out MYPK.RCT1)
AS
begin
OPEN RC1 FOR
SELECT
FIELD1,FIELD2......FIELDN
FROM MYTABLE
WHERE FIELDJ = PARAM1;
end;

Notice that the stored procedure requires the cursor (RC1)as an output parameter of the type defined in the package.

OK, I have done this, defined both of them. If I execute the procedure from a command-line client (sql*plus) it works, the cursor contains the data.

But how on earth do I "sqlexec" this? What kind of FoxPro beast is an Oracle cursor?

That is, now I need to
sqlexec (myconn,"execute myproc (?myvar,WHAT_HERE)","result")

No matter what I put in WHAT_HERE, it of course fails.

Thanks in advance





I am trying to modify a VFP application than runs against an MS SQL server, so that it can run against an Oracle 8i database.

I have not been able to figure out what to do with SQL Server stored procedures that return a result set. Oracle suggests that I define a cursor in a package and a procedure that returns this cursor. But how do I create a cursor variable in VFP and pass it to Oracle?


That is, I need to do:
sqlexec(conn,"exec sp_something(curvar)")
where sp_something expects a (oracle) cursor variable.

Any help or pointers will be appreciated