>Hello Everyone!!
>I am starting a new project that will pull information directly from Oracle. I have been using SQL PASSTHROUGH for the task. However, it has now come to my attention that some of the columns that we thought were in the table are really made up from some packages that were previously written.
>For example to get the commodity column, if we were on oracle we would call the package with the Parameter of the product_code inside of a select statment and it will spit out the commodity.
>
>Question- In side of VFP's SQL passthrough, can I call a package in my sqlexecute statement or is it a lost cause?
>
>Thanks in advance
Yes you can call the package and have it return a result but IMO, it is better to simply send the SQL statement through via SPT. Basically it looks like this:
lnconn = sqlstringconnect("Driver={Oracle ODBC Driver};Server=Server.Domain;DBQ=Server.Domain;UID=user;PWD=password")
lnresult = sqlexec(lnconn,"{call MyPackage.MyFunction}","c_mycursor")
The only way I have found to return a result set from an SP or package is to create a cursor in Oracle and have the function return that cursor. This is
exceedingly slow!! About 13x slower than simply passing in the SQL and having Oracle parse/optimize it on-the-fly.
HTH.
Larry Miller
MCSD
LWMiller3@verizon.netAccumulate learning by study, understand what you learn by questioning. -- Mingjiao