stored procedures called from Visual Fox Pro

stored procedures called from Visual Fox Pro

Post by Oscar Golde » Sun, 31 Dec 1899 09:00:00

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
However, I am disappointed with the way Oracle handles stored procedures
that return result sets.

Suppose this:

Stored procedure definition in MS-SQL:



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;

create or replace procedure myproc (
param1 char,rc1 out mypk.rct1)
open rc1 for
from mytable
where fieldj = param1;

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