by 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:
as
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...