title: Fast way to count rows of a cursor
body: <p>I have some data retrieving logic into a stored procedure.
  which returns a cursor with the content of an SQL query defined in this stored procedure.</p>
  
  <p>Something of this format :</p>
  
  <pre><code>function search_foo(param1 varchar2, param1 varchar3)
    return g_ref -- a ref cursor
    is
      l_return g_ref; 
  {
    open l_return for
      select col1_i_need, col2_i_need 
      from foo
      join bar on --[...]
      where [...];
  
      return l_return;
  }
  </code></pre>
  
  <p>But when the dataset returned is huge, I want to count the number of rows before fetching all the dataset (if too much rows, stop the retrieving).</p>
  
  <p>So the easy way to do that could be to define another function like that</p>
  
  <pre><code>function search_foo(param1 varchar2, param1 varchar3)
    return number
    is
      l_return number; 
  {
  
      select count(*) into l_return
      from foo
      join bar on --[...]
      where [...];
  
      return l_return;
  }
  </code></pre>
  
  <p>But it seems bad : code redondancy and so it increase the risk of forgotting one of the method when an update has to be made.</p>
  
  <p>So my question is : Is it possible to make a stored procedure who returns the number of rows of a returned by another stored procedure ?</p>
  
  <p>How can I refactor the SQL code of my query for being able to count the rows ? The easiest but solution would be to write 2 separate SQL queries : one for counting and one for getting the data but this seems ugly to me (code redondancy and so more bugs risk).</p>
  
  <p>Thanks by advance.</p>