3

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.

hmartos
  • 861
  • 2
  • 10
  • 19
  • 2
    Possible duplicate of [Number of rows affected by an UPDATE in PL/SQL](http://stackoverflow.com/questions/861983/number-of-rows-affected-by-an-update-in-pl-sql) Should work with `Insert` too – Juan Carlos Oropeza Oct 08 '15 at 15:48
  • 1
    There is no exact pure SQL equivalent that I know of. You can do this from PL/SQL, but I'm not sure it's what you'll want. If you provide a bit more context on how you'll be executing the query and what is the reason you need the count, there may be alternatives that you can use. – sstan Oct 08 '15 at 15:48
  • Thank you @sstan, that is what I wanted to know, I did it with a workaround doing ```SELECT COUNT(*) FROM (QUERY)```. I discarted this approach because it didn't work on SQL Server if the query contains an ORDER BY clause, but it seems to be working fine on Oracle – hmartos Oct 08 '15 at 15:59

1 Answers1

6

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
sstan
  • 35,425
  • 6
  • 48
  • 66
  • I have to agree with Juan's comment above that this is a repeat of [Number of rows affected by an UPDATE in PL/SQL](https://stackoverflow.com/questions/861983/number-of-rows-affected-by-an-update-in-pl-sql). The answer given there is a much better answer without any workarounds. – David Storfer Mar 06 '20 at 18:28