Re: Using ROWNUM

In comp.databases.oracle.misc Reds <redex1398_at_hotmail.com> wrote:
> I'm having a lot of problems using ROWNUM. Specifically, using ROWNUM in
> the where clause to access a particular row. It seem that if I use it
> as "ROWNUM = 1 " or "ROWNUM > 0", it works.
> But when I use it as "ROWNUM = 2" or "ROWNUM > 1", no rows are selected.

ROWNUM enumerates the rows that are actually returned by the query. That is, the whole query *including* the WHERE clause that refers to ROWNUM.

So if your say "WHERE ROWNUM = 2", Oracle can never return a row to you because the first row that you would get would have ROWNUM = 1 and you exclude that row. And no second row can be returned unless a first row is returned.

> BTW, does anyone has a suggestion as to how to use the SELECT INTO to
> sequentially access each row? I thought the ROWNUM would be a good
> parameter to use, but it's not cooperating.

You can either use BULK COLLECT to retireve multiple results into a collection and loop through the results, or you can use cursor FOR loops, or you can use explicit cursors.

See http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqloperations.htm#sthref1345

Yours,
Laurenz Albe Received on Thu Oct 04 2007 - 04:09:28 CDT