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.