I just saw this thread...
If you are getting started in Oracle, then here are a few suggestions:
1. Get the O'Reilly book on PL/SQL by Steve Feurstein.
2. Get PL/SQL Developer as your client tool for working with Oracle. It is by AllAroundAutomations. A great tool for $150.
3. If possible, use Oracle's driver - not the MS Oracle Driver.
4. Consider using stored procedures. As you can see, when you start working with Oracle Functions, your client code will start to get messy. In Oracle, you can either create stand-alone procs OR - you can encapsulate procs and functions in packages. I prefer the latter because it approximates a class scenario.
Here is sample package spec and body:
create or replace package FoxOracle is
Type Resultset is ref cursor;
procedure GetSales(cCustomer in sales.customer%type,
dOrderDate in sales.orderdate%type,
iStatus out int,
curSales out FoxOracle.Resultset,
);
end ;
create or replace package body FoxOracle is
procedure GetSales(cCustomer in sales.customer%type,
dOrderDate in sales.orderdate%type,
iStatus out int,
curSales out FoxOracle.Resultset,
)
is
begin
Open cursales for
Select *
from sales
Where customer = cCustomer and
orderdate >= dOrderDate
Order By OrderDate;
iStatus := 1;
Exception
When no_data_found Then
iStatus := -1;
end GetSales;
end;
Your Fox Code is then reduced to:
nResult = sqlexec(nHandle,"{call foxoracle.getsales(?lcCust,?ldDate,?@liStat)}","cursales")
Just make sure to make your vars private...
>Thanks for the help.
>
>Jerry
>
>
>>Glad it worked.
>>
>>Yes, TO_DATE() is an ORACLE function. You'll find TO_CHAR() equally usefull.
>>
>>lc_Sql =
[SELECT * FROM Sales WHERE TO_CHAR(Sales.OrderDate, 'YYYY') = '] + TRANS(YEAR(DATE())) + ['] && Gets all records for the current year.
>>
>>I myself am just getting started in Oracle. You find that Mark M. will be a big help.
>>
>>Mike.