The problem here is that Oracle cannot know how many rows are going to
be returned with actually getting them.

However, don't loop over all the rows once to get the row count, and
then again to get the rows you want - that will be very slow.

Create a new query which is

select count(*)
from <the rest of you query>

ie, the exact same where clause as you actual select.  The count(*)
will be much faster than actually retriving the data.

Then open the original cursor to get the rows you want.  The best way
to limit the rows here is to use rownum - in this was you can bring
back a 'window' of results from the database:-

 1  select *
 2  from
 3  (
 4    select * from
 5    (
 6      select rownum rnum, ename
 7      from emp
 8      order by ename
 9    ) where rnum <= 5
10* ) where rnum >= 2

This query will return rows 2 to 5 inclusive.

 1  select *
 2  from
 3  (
 4    select * from
 5    (
 6      < ** your entire query goes here including order by ** >
 9    ) where rnum <= max_row
10* ) where rnum >= min_row

Obviously this means you need to have two ref cursors, but it will
probably be faster.

One other option you could try is:

 1  select *
 2  from
 3  (
 4    select * from
 5    (
 6      select rownum rnum, ename, count(*) over ()
 7      from emp
 8      order by ename
 9    ) where rnum <= 5
10* ) where rnum >= 2

In this case the total count will be on each row returned, and you
could grand it on the first pass of processing the results.
I am not sure if this would be more efficient - but I guess I can test
it out while I am here:

SQL> create table big_table (id integer, value varchar2(255))
 2  ;

Table created.

SQL> insert into big_table select object_id, object_name from
all_objects;

30314 rows created

SQL> select count(*) from big_table;

 COUNT(*)
----------
    30314

Statistics
----------------------------------------------------------
         1  recursive calls
         1  db block gets
       188  consistent gets
         0  physical reads
       120  redo size
       381  bytes sent via SQL*Net to client
       503  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         1  rows processed

 1  select *
 2  from
 3  (
 4    select *
 5    from
 6    (
 7      select rownum rnum, id, value
 8      from big_table
 9      order by value
10    ) where rnum <= 45
11* ) where rnum >= 1

45 rows selected.

Statistics
----------------------------------------------------------
         0  recursive calls
         4  db block gets
       184  consistent gets
       156  physical reads
         0  redo size
      2465  bytes sent via SQL*Net to client
       525  bytes received via SQL*Net from client
         4  SQL*Net roundtrips to/from client
         0  sorts (memory)
         1  sorts (disk)
        45  rows processed

So thats 184 + 184 = 268 consistent gets for this case.

 1  select *
 2  from
 3  (
 4    select *
 5    from
 6    (
 7      select rownum rnum, id, value, count(*) over ()
 8      from big_table
 9      order by value
10    ) where rnum <= 45
11* ) where rnum >= 1

Statistics
----------------------------------------------------------
         0  recursive calls
         6  db block gets
       184  consistent gets
       471  physical reads
         0  redo size
      2619  bytes sent via SQL*Net to client
       525  bytes received via SQL*Net from client
         4  SQL*Net roundtrips to/from client
         0  sorts (memory)
         1  sorts (disk)
        45  rows processed

So, only 184 consistent gets in total - but there are many more
physical reads here for some reason.  I am afraid I am not sure why -
could it to be with the sorting?

I guess you could try both methods and see which performs better for
you data and where clause etc.

Stephen.