Hola Oscar

¿Cómo estais? ¿Que tal la vida por Buenos Aires?

I posted the following a couple of months back in response to the same question. I should have taken John Koziol's hint and put it in a FAQ since it does come up from time to time. A procedure in versions of Oracle prior to 8 cannot return a value. Only a function can. Functions (and procedures in 8.x) that return values usable by FoxPro have to be in a package and have to be accompanied by an appropriate PRAGMA statement in the package header. The Pragma is basically a promise not to use the procedure or function to change the tables or global variables that it uses. In Oracle versions prior to 8, you can't even write to a PL/SQL cursor (an array)! But, given a package with the adequate pragma and function (or procedure in Oracle 8.x) this is what you'd do:

Oracle has a dummy table called Dual that can be used in this situation. Make you're code look something like this:

lnOk = SQLEXEC(gnhandle,"SELECT OracleFunction(parameter) FROM DUAL","MyCursor")

That will run the function and put the results in MyCursor for you to work with. Using Dual is a pretty standard thing within Oracle and PL/SQL. I just tested this and it works great. Most of the things I do between Oracle and VFP work better with views.

I am able to use the same 'dummy' table to run functions from within a VFP remote view as well. Assuming you have created a database to contain the view and have created a connection 'myoracledb' into Oracle, you could do something like this:

CREATE SQL VIEW "VIEW1" ;
REMOTE CONNECTION "myoracledb" ;
AS SELECT packagename.function(parameter) FROM DUAL

and that will return the value as well. You can try to do this interactively but I doubt that you will have much luck. You can do more things (like this) through code than the VFP View Designer will allow you to do.

One note about functions and procedures in Oracle: In versions prior to Oracle 8, you can't use a procedure to return a value in this way. You have to use a function. Supposedly (I haven't done this myself since I'm stuck using Version 7.3) procedures will return a value in Oracle 8 and later.

Espero que eso le ayude.
I hope this helps.

Gregg