Find out if query exceeds arbitrary limit using ROWNUM?

I have a stored proc in Oracle, and we're limiting the number of records with ROWNUM based on a parameter. However, we also have a requirement to know whether the search result count exceeded the arbitrary limit (even though we're only passing data up to the limit; searches can return a lot of data, and if the limit is exceeded a user may want to refine their query.)

The limit's working well, but I'm attempting to pass an OUT value as a flag to signal when the maximum results were exceeded. My idea for this was to get the count of the inner table and compare it to the count of the outer select query (with ROWNUM) but I'm not sure how I can get that into a variable. Has anyone done this before? Is there any way that I can do this without selecting everything twice?

Thank you.

EDIT: For the moment, I am actually doing two identical selects - one for the count only, selected into my variable, and one for the actual records. I then pass back the comparison of the base result count to my max limit parameter. This means two selects, which isn't ideal. Still looking for an answer here.

Solution

You could use a nested subquery:

select id, case when max_count > 3 then 'Exceeded' else 'OK' end as flag
from (
 select id, rn, max(rn) over () as max_count
 from (
    select id, rownum as rn
    from t
  )
  where rownum <= 4
)
where rownum <= 3;

The inner level is your actual query (which you probably have filters and an order-by clause in really). The middle later restricts to your actual limit + 1, which still allows Oracle to optimise using a stop key, and uses an analytic count over that inner result set to see if you got a fourth record (without requiring a scan of all matching records). And the outer layer restricts to your original limit.

With a sample table with 10 rows, this gets:

        ID FLAG    
---------- --------
         1 Exceeded 
         2 Exceeded 
         3 Exceeded

If the inner query had a filter that returned fewer rows, say:

select id, rownum as rn
from t
where id < 4

it would get:

        ID FLAG    
---------- --------
         1 OK       
         2 OK       
         3 OK

Of course for this demo I haven't done any ordering so you would get indeterminate results. And from your description you would use your variable instead of 3, and (your variable + 1) instead of 4.