Oracle Cursor fetch using ODAK

[mtawk]

Hello,

I need help in Using ODAC SQL to :

1- Get a block of records from a SQLQuery (A number of records starting from a specific row number, like FETCH ABSOLUTE in SQL server)

2- Get the RecordCount of the Current Query.

Ex:
Select * from T1
Inner join T2 on T1.key = T2.key
…………………..
Inner join Tn on Tn.key = Tx.key
Where conditions
Etc …

Actually I'm using a solution but it seems to be very slow.
There must be a better solution by ODAC with Oracle.

What I’m doing is executing 2 Queries.

PS: the variable The_Initial_Query used in the 2 below queries is the initial Query with the multiple joins given in the abose exemple.

1: to get The RecordCount:
select count(*) from ( The_Initial_Query ) .

2 : to get my records’ block :
select * from ( select ROWNUM RN, sub.* from ( The_Initial_Query ) sub where ROWNUM = OffSetRow and RN < (OffSetRow + TopRowCount).

Where:
OffSetRow : the Start Row
TopRowCount is the row count that I Want as a result

my questions are :
1- What is the best way to do this in ODAC based on a oracle cursor without going through with this slow query?

2- How can i get the row count of a certain query without going through a select count(*) from ( The_Initial_Query ) ?


Any help would be appreciated.

[Alex]

You solution is almost right, but the better way is to remove high boundary limitations from your final SQL statement and break fetching on the client side, in other words your SQL statement should look like this:

Code: Select all

"SELECT Rn, FQ.* FROM (SELECT IQ.*, RowNum AS Rn FROM (The_Initial_Query) IQ ) FQ WHERE Rn > 50"

and on the client side you should stop fetching by setting Cancel variable in BeforeFetch event handler of your DataSet object, for example:

Code: Select all

procedure TForm1.OraQuery1BeforeFetch(DataSet: TCustomDADataSet;
 var Cancel: Boolean);
begin
 Cancel:= OraQuery1.RowsProcessed > TopRowCount;
end;

Also you need to know that FetchRows property of your DataSet object determines number of rows that will be transferred to the client side at the same time, so you can get some more records instead exact number(see "TCustomDADataSet.FetchRows" topic in ODAC Help).

Note : To use this solution it is recommended to optimize a query for retrieving a first row, otherwise using this method is ineffective.