Re: Select * where rownum betweenFirstRec and LastRec

>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);
>

If you make your procedure part of a package, you can declare a package variable that you save the value in. Then, you can use this in subsequnt calls to establish the starting point (but what happens if someone else inserts/deletes a row in the batch you just read?)

Dan Hekimian-WIlliams Received on Thu Sep 07 2000 - 06:35:28 CEST