Get count of ref cursor in Oracle

Question

I have a procedure which returns ref cursor as output parameter. I need to find a way to get the count of no.of records in the cursor. Currently I have count fetched by repeating the same select query which is hindering the performance.

ex:

create or replace package temp 
TYPE metacur IS REF CURSOR;

PROCEDURE prcSumm (
pStartDate          IN          DATE,
pEndDate            IN          DATE,
pKey        IN          NUMBER,
pCursor             OUT         metacur
) ;

  package body temp is

  procedure prcSumm(  
  pStartDate          IN          DATE,
  pEndDate            IN          DATE,
  pKey        IN          NUMBER,
  pCursor             OUT         metacur
   )
 IS
 vCount NUMBER;

 BEGIN

  vCount := 0;

select count(*) into vCount
   from customer c, program p, custprog cp
   where c.custno = cp.custno
   and cp.programid = p.programid
   and p.programid = pKey
   and c.lastupdate >= pStartDate
   and c.lastupdate < pEndDate;

 OPEN pCursor for SELECT 
  c.custno, p.programid, c.fname, c.lname, c.address1, c.address2, cp.plan               
   from customer c, program p, custprog cp
   where c.custno = cp.custno
   and cp.programid = p.programid
   and p.programid = pKey
   and c.lastupdate >= pStartDate
   and c.lastupdate < pEndDate;
    end prcSumm;

Is there a way to get the no.of rows in the out cursor into vCount.

Thanks!

Answer 1

Oracle does not, in general, know how many rows will be fetched from a cursor until the last fetch finds no more rows to return. Since Oracle doesn't know how many rows will be returned, you can't either without fetching all the rows (as you're doing here when you re-run the query).

Unless you are using a single-user system or you are using a non-default transaction isolation level (which would introduce additional complications), there is no guarantee that the number of rows that your cursor will return and the count(*) the second query returns would match. It is entirely possible that another session committed a change between the time that you opened the cursor and the time that you ran the count(*).

If you are really determined to produce an accurate count, you could add a cnt column defined as count(*) over () to the query you're using to open the cursor. Every row in the cursor would then have a column cnt which would tell you the total number of rows that will be returned. Oracle has to do more work to generate the cnt but it's less work than running the same query twice.

Architecturally, though, it doesn't make sense to return a result and a count from the same piece of code. Determining the count is something that the caller should be responsible for since the caller has to be able to iterate through the results. Every caller should be able to handle the obvious boundary cases (i.e. the query returns 0 rows) without needing a separate count. And every caller should be able to iterate through the results without needing to know how many results there will be. Every single time I've seen someone try to follow the pattern of returning a cursor and a count, the correct answer has been to redesign the procedure and fix whatever error on the caller prompted the design.