I have an application for query management. Previously I was using SQL Server database, and to get the number of affected rows by a query I used to do:
SELECT * FROM TABLE
(or any other select query)
and then I do SELECT @@ROWCOUNT
to get the number of rows affected by the last executed query.
I have read about SQL%ROWCOUNT
, but I am not able to make it work in a SELECT statement
Is there any way to do this in a Oracle database?. Thank you!
Edited:
I have solved this by doing SELECT COUNT(*) FROM (QUERY)
to get the number of rows affected by the query, I discarted this approach because it was not working in SQL Server if the query contains an ORDER BY
clause.
I don't know of any exact equivalent in Oracle that you can use in pure SQL.
An alternative that may work for you, depending on your specific need, is to add a count(*) over ()
to your select
statement to give you the total number of rows. It would at least save you from having to re-execute the query a 2nd time.
select t.*,
count(*) over () as num_rows
from table t
where ...
Or, if you can't change the original query, then you can wrap it like this:
select t.*,
count(*) over () as num_rows
from (query) t