Can RowNum be used in ANY query
David Schwartz, October 15, 2002 - 3:34 pm UTC
Can I simply append "And Rownum <= 500 " to any & every query string? Are there special cases/exceptions that I need to look out for?
October 15, 2002 - 3:49 pm UTC
you want to take a query "QUERY" and wrap it
select * from ( QUERY ) where rownum < 500;
IMPORTANT, wrap it front and back -- don't just ADD "and rownum <= 500"
Question
H, October 15, 2002 - 3:56 pm UTC
why wrap it front and back -- don't just ADD "and rownum <= 500"
please explain - thanks
October 15, 2002 - 4:01 pm UTC
select * from t
hmmm, bummer AND rownum <=500 isn't right.
select job, count(*) from emp group by job
hmmm, bummer AND rownum <=500 isn't right.
and so on. That -- and "ORDER BY" / "GROUP BY" would be done AFTER a predicate so:
select a,b from t where rownum <= 500 order by a;
does this:
o get 500 random rows
o sort it
whereas
select * from ( select a,b, from t order by a) where rownum <= 500;
does this:
o "order" the data
o return the first 500 rows AFTER sorting
different answers
How can I determine the actual total rows
David Schwartz, October 15, 2002 - 7:37 pm UTC
That's great - thanks.
Now how can I find out what hte actual number of rows were, assuming the query returns less than 500?
Is there a system var set that I can access?
October 16, 2002 - 8:22 am UTC
depends on the environment. In PLSQL
cursor_name%rowcount
in pro*c sqlca.sqlerrd[2]
and so on.
Accessing sql%rowcount
David Schwartz, October 17, 2002 - 11:24 am UTC
I'm using ColdFusion.
this query...
select * from
(
select * from emp
)
Where RowNum <= 10
works & finds 10 rows.
How can I access the sql%rowcount var?
October 17, 2002 - 2:00 pm UTC
guess you'll have to ask the makers of cold fusion eh? it is their "language" you are using after all. sql%rowcount is in PLSQL (sqlca.sqlerrd[2] in pro*c, something else in jdbc, etc etc etc)
Generic RowCount
David Schwartz, October 17, 2002 - 2:40 pm UTC
ok - forget coldfusion (should be easy)....
how can I access it from a standard, generic query?
for example - can I do something like...
Select * From Emp
Select sql%rowcount
Maybe this will help clarify my intentions - In Sybase I would simply run Select @@rowcount after a query.
October 17, 2002 - 2:52 pm UTC
As I keep saying -- it depends on the ENVIRONMENT.
Unlike Sybase, we use cursors (in sybase if you used a cursor, a "simple" select @@rowcount returns WHAT exactly?)
In plsql:
scott@ORA920.US.ORACLE.COM> declare
2 cursor c is select * from emp;
3 begin
4 for r in c
5 loop
6 dbms_output.put_line( C%rowcount || ' rows fetched so far....' );
7 end loop;
8 end;
9 /
1 rows fetched so far....
2 rows fetched so far....
3 rows fetched so far....
So, I guess that is what you are looking for.
Implicit Cursors
David Schwartz, October 17, 2002 - 3:09 pm UTC
I thought Oracle always uses a cursor - for every query - by default. So I figured there was a built in function/var that would store the last cursor rowcount.
Then I could just query for it. Since it's not C/SQL+/Java etc. a lot of the interactivity features are lacking.
Am I going down the wrong path?
October 17, 2002 - 3:16 pm UTC
Yes, you are.
There is no such concept here. It is 100% dependent on the language how you would access this. Each cursor has a state, a context. We expose this information in each and every language ( <cursor>%rowcount in plsql, sqlca.sqlerrd[2] in pro*c and so on).
See, the funny thing is -- in order to parse a query like "select @@rowcount from dual" - we might have to run 50 queries for you (recursive sql) to parse it. So, the "last cursor" wouldn't be yours -- or maybe it would.
Explicit cursor and %rowcount
A reader, October 21, 2003 - 7:06 am UTC
Good morning Tom,
I have gone through PL-SQL user guide, but things still not very clear.
In one of the stored procedures, I am
1. opening a cursor
2. fetching data in a record using loop
3. some processing and insert/update a table
4. end loop
5. check for %rowcount. If %rowcount = 0, set all variables to zero and insert/update a table
6. close cursor
I assume that %rowcount will have number of rows fetched till end of loop. Is my assumption correct?
Thanks!!!
October 21, 2003 - 4:55 pm UTC
cursor%rowcount will have the total rows fetched, yes.
but, i would just use an implicit cursor and set a boolean inside the loop -- easier to code.
or, use select ...bulk collect and look at array.count to see how many you got
or, using an explicit cursor -- bulk collect anyway.
How do we get rows from 20 - 50
RB, January 21, 2004 - 12:14 am UTC
Is there an easy way in oracle to retrieve data from row 20 to row 50 or n to n + 10
January 21, 2004 - 6:35 am UTC