| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Using ROWNUM
On Oct 4, 3:56 am, Reds <redex1..._at_hotmail.com> wrote:
> Hi,
> 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.
>
> Here are some query examples:
>
> THIS WORKS:
>
> SQL> SELECT *
> 2 FROM Student
> 3 WHERE ROWNUM = 1;
>
> STU_ID LNAME FNAME M S MAJOR HOME
> ---------- ---------- ---------- - - ---------- ----
> 10001 Smith Ron M M Math Tx
>
> SQL> SELECT *
> 2 FROM Student
> 3 WHERE ROWNUM > 0;
>
> STU_ID LNAME FNAME M S MAJOR HOME
> ---------- ---------- ---------- - - ---------- ----
> 10001 Smith Ron M M Math Tx
> 10002 Jones Peter A M English Tx
> 10003 Peters Anne A F English Me
> 10004 Johnson John J M CompSci Ca
> 10005 Penders Alton P F Math Ga
> 10006 Allen Diane J F Geography Minn
> 10007 Gill Jennifer F F CompSci Tx
> 10008 Johns Roberta A F CompSci Ala
> 10009 Wier Paul A M Math Ala
> 10010 Evans Richard A M English Tx
>
> 10 rows selected.
>
> BUT THIS DOESN'T WORK:
>
> SQL> SELECT *
> 2 FROM Student
> 3 WHERE rownum > 1;
>
> no rows selected
>
> SQL> SELECT *
> 2 FROM Student
> 3 WHERE ROWNUM = 2;
>
> no rows selected
>
> ******************************************************
> Here are some PL/SQL exampleS
>
> THIS WORK:
>
> SQL> set serveroutput on
> SQL> DECLARE
> 2
> 3 v_ID Student.Stu_ID%TYPE;
> 4 v_Rowcount NUMBER := 1;
> 5
> 6 BEGIN
> 7
> 8 LOOP
> 9 SELECT Stu_ID
> 10 INTO v_ID
> 11 FROM Student
> 12 WHERE ROWNUM = 1;
> 13 DBMS_OUTPUT.PUT_LINE('Row Number : '||v_Rowcount||', Student ID:
> '|| v_ID)
> ;
> 14 v_Rowcount := v_Rowcount + 1;
> 15
> 16 EXIT ;
> 17 END LOOP;
> 18 END;
> 19 /
> Row Number : 1, Student ID: 10001
>
> PL/SQL procedure successfully completed.
>
> BUT THIS DOESN'T WORK:
>
> SQL> DECLARE
> 2
> 3 v_ID Student.Stu_ID%TYPE;
> 4 v_Rowcount NUMBER := 1;
> 5
> 6 BEGIN
> 7
> 8 LOOP
> 9 SELECT Stu_ID
> 10 INTO v_ID
> 11 FROM Student
> 12 WHERE ROWNUM = 2;
> 13 DBMS_OUTPUT.PUT_LINE('Row Number : '||v_Rowcount||', Student ID:
> '|| v_ID)
> ;
> 14 v_Rowcount := v_Rowcount + 1;
> 15
> 16 EXIT ;
> 17 END LOOP;
> 18 END;
> 19 /
> DECLARE
> *
> ERROR at line 1:
> ORA-01403: no data found
> ORA-06512: at line 9
>
> 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.
>
> Thanks
ROWNUM isn't what you apparently think it is. A quick read of the documentation and the Concepts Manual would have shed some light on this (as would a quick search of this newsgroup's archives):
ROWNUM is assigned to the rows in a result set; it is not assigned BEFORE any rows are successfully returned, therefore any WHERE clause like:
WHERE rownum = 2
will ALWAYS fail as ROWNUM 1 has yet to be assigned (so there is no ROWNUM = 2 and, as you've discovered, no rows are returned):
SQL> select owner, object_name
2 from dba_objects
3 where rownum = 21;
no rows selected
SQL> Conditionals such as:
WHERE ROWNUM > 0
WHERE ROWNUM <= 5
WHERE ROWNUM < 10
will return results as the ROWNUM mechanism can successfully assign ROWNUM values as a result set is generated:
SQL> select owner, object_name
2 from dba_objects
3 where rownum < 21;
OWNER OBJECT_NAME ------------------------------ --------------------------------- SYS ACCESS$ SYS ALL_ALL_TABLES SYS ALL_ARGUMENTS SYS ALL_ASSOCIATIONS SYS ALL_CATALOG SYS ALL_CLUSTERS SYS ALL_CLUSTER_HASH_EXPRESSIONS SYS ALL_COLL_TYPES SYS ALL_COL_COMMENTS SYS ALL_COL_PRIVS SYS ALL_COL_PRIVS_MADE OWNER OBJECT_NAME ------------------------------ --------------------------------- SYS ALL_COL_PRIVS_RECD SYS ALL_CONSTRAINTS SYS ALL_CONS_COLUMNS SYS ALL_CONTEXT SYS ALL_DB_LINKS SYS ALL_DEF_AUDIT_OPTS SYS ALL_DEPENDENCIES SYS ALL_DIMENSIONS SYS ALL_DIM_ATTRIBUTES
20 rows selected.
SQL> ROWNUM is also assigned to the rows AS they are returned BEFORE any order by operation:
SQL> select rownum, file_name
2 from dba_data_files
3 where rownum < 11
4 order by file_name;
ROWNUM FILE_NAME
---------- ----------------------------------------
7 /d40/oradata/orap2p04/p2000ap_04.dbf
9 /d40/oradata/orap2p04/pidm_well_09.dbf
8 /d40/oradata/orap2p04/pidm_well_10.dbf
6 /d40/oradata/orap2p04/sdest03_01.dbf
4 /d40/oradata/orap2p04/sdest03_02.dbf
3 /d40/oradata/orap2p04/sdest03_03.dbf
5 /d40/oradata/orap2p04/sdesx03_01.dbf
2 /d40/oradata/orap2p04/sdesx03_02.dbf
1 /d40/oradata/orap2p04/sdesx03_03.dbf
10 /d40/oradata/orap2p04/utlstats01.dbf
10 rows selected.
SQL> This can be 'circumvented' with an in-line view:
SQL> select rownum, f.file_name
2 from
3 (select file_name
4 from dba_data_files
5 order by file_name) f
6 where rownum < 11
7 /
ROWNUM FILE_NAME
---------- ---------------------------------------------
1 /d40/oradata/orap2p04/PATROL.dbf
2 /d40/oradata/orap2p04/adl_codes_01.dbf
3 /d40/oradata/orap2p04/adl_codes_idx_01.dbf
4 /d40/oradata/orap2p04/adl_prod_01.dbf
5 /d40/oradata/orap2p04/adl_prod_02.dbf
6 /d40/oradata/orap2p04/adl_prod_03.dbf
7 /d40/oradata/orap2p04/adl_prod_04.dbf
8 /d40/oradata/orap2p04/adl_prod_idx_01.dbf
9 /d40/oradata/orap2p04/adl_prod_idx_02.dbf
10 /d40/oradata/orap2p04/adl_prod_idx_03.dbf
10 rows selected.
SQL> To answer your question about SELECT INTO your assumption is there is a defined order to heap table; nothing could be further from the truth, really. If you want order from a heap table then use ORDER BY as you won't get it any other way. And ROWNUM isn't a good 'index' for your 'sequential SELECT INTO' issue; a basic PL/SQL loop will be your best mechanism to process one row at a time, in whatever order they are presented at query time.
For more information on this and other similar topics visit:
He has a wealth of examples to help you.
David Fitzjarrell Received on Thu Oct 04 2007 - 08:15:14 CDT
 |
 |