I have an application that is currently using VFP tables. I am in the process of porting this application to use Oracle on the backend. The current application allows navigating through the data on as many as 12 different orders by selecting top, next, previous and last. The users would like to retain this functionality when the data is moved into oracle.

I am presently using SQL pass through due to the large number of orders that are possible. The following code works for small tables all the time but on the primary table that is approx. 100,000 rows returns an error sometimes. Is there a better way to provide this functionality?

*----- Example retrieves next row
*----- Previous is the same except uses < and DESC
*----- Top and Last does not use value, Last uses DESC

CURSORSETPROP('MaxRecords',1,0) && Set the number of rows to
&& return to 1

cTable=THIS.sql_table
cOrder=THIS.sql_order
cCursor=THIS.sql_cursor
cValue=THIS.sql_parse(THIS.sql_order)
nHandle=THIS.sql_handle

sql_trans=[SELECT * FROM ]+cTable+ ;
[ WHERE ]+cOrder+[ > ]+cValue+ ;
[ ORDER BY ]+cOrder+[ ASC]

SQLPREPARE(nHandle,sql_trans,cCursor)
SQLEXEC(nHandle)

CURSORSETPROP('MaxRecords',-1,0) && Set the number of rows to
&& return to unlimited

Thanks,

Greg