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.
I hope this helps.
>I am using SQL passthru that needs to make a call to an
>Oracle function. The Oracle function will return a value (number
>let's say 15).
>
>So I need to make the SQL passthru do something like
>this:
>
>lnOk = SQLEXEC(gnhandle,"{call OracleFunction()}")
>
>The problem is that lnOk contains the result of the
>SQLEXEC. That is -1 if a problem, 0 is running and
>1 if it was successful. I need the 15 or whatever
>it is on the next call instead of the -1, 0 or 1.
>
>Can I capture the value of '15' from the Oracle function?
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only