CODEWITHSUNDEEP
sqloracle-databaserowcount
hmartos
hmartos

Reputation: 919

SELECT @@ROWCOUNT Oracle equivalent

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.

Upvotes: 3

Views: 25492

Answers (1)

sstan
sstan

Reputation: 36483

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

Upvotes: 6

Related Questions