Re: Select * where rownum betweenFirstRec and LastRec

Hi,

Actually it will never work like that because rownum accounts for the current retrieved record and the only valid predicate in this case is rownum < 21 (or x). So you can restrict the overall number of records retrieved but you can't be in control of the record pointer positioning using rownum. You can do that by creating a cursor and retrieving chunks of 20 records at a time using a cursor pointer for example.

"Robert Vabo" <robert.vabo_at_gecko.no> wrote in message news:aXkt5.1070$N4.348331_at_juliett.dax.net...
> I have written a stored procedura that are going to perform a search and
> return x records. Next time it is going to return the next y records.
>
> Before this code I have created a table and filled it with data.
>
> Eg. FromRec = 21 and LastRec = 40
> I have read some articles that tels me thatthis won't work because rownum
> between x and y, x has to be 1.
>
> Any suggestions on how to make this work ??
>
> cursor01 := DBMS_SQL.OPEN_CURSOR;
> strGetRecordsLeft := 'SELECT COUNT (*) FROM ' || TempTableName || ' WHERE
> ROWNUM BETWEEN ' || FromRec || ' AND ' || LastRec;
> dbms_output.put_line(strGetRecordsLeft);
> dbms_sql.parse (cursor01, strGetRecordsLeft, DBMS_SQL.NATIVE);
> dbms_sql.define_column (cursor01, 1, AntPosterIgjen);
> dbms_sql_feedback := dbms_sql.execute_and_fetch (cursor01);
> dbms_sql.column_value (cursor01, 1, AntPosterIgjen);
> dbms_sql.close_cursor (cursor01);
>
> --
> Regards
> Robert Vabo
> Application developer
> Gecko Informasjonssystemer AS
> www.gecko.no
> robert.vabo_at_gecko.no
>
>
Received on Wed Sep 06 2000 - 01:32:03 CDT