Gregg,
Outstanding post. Would you consider writing this up into a KB "How-To" and posting it here in the KB section.
>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?
------------------------------------------------
John Koziol, ex-MVP, ex-MS, ex-FoxTeam. Just call me "X"
"When the going gets weird, the weird turn pro" - Hunter Thompson (Gonzo) RIP 2/19/05