stored procedures called from Visual Fox Pro

I have a Visual Fox Pro 6.0 application that runs against a MSSQL server, with no problems.
I need to modify it so it can be used with either MSSQL or Oracle (8i/NT) (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 in a package 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 name (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 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 in Fox to:
sqlexec (myconn,"execute myproc (?myvar,WHAT_HERE)","result")

No matter what I put in WHAT_HERE, it of course fails. The SP does not like what it finds there.

Thanks in advance. I have posted this in VFP newsgroups with little success... Received on Wed Nov 17 1999 - 09:07:23 CST