Fast way to count rows of a cursor

Question

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.

Something of this format :

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;
}

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).

So the easy way to do that could be to define another function like that

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;
}

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.

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 ?

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).

Thanks by advance.

I think the answer to your question is yes. However: Sharing your research helps everyone. Tell us what you've tried and why it didn’t meet your needs. This demonstrates that you’ve taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer! Also see how to ask — Scott Solmer
– Scott Solmer, Commented Nov 18, 2014 at 16:01
have you try using %rowcount cursor attribute, i think this is what you may be looking for — Aramillo
– Aramillo, Commented Nov 18, 2014 at 16:04
Using %rowcount need to fetch the cursor right ? I'm searching for a solution to use the same SQL code in 2 stored procedures : one for giving the data set (which can be slow) and one for only giving the rowcount (which has to be fast). — eka808
– eka808, Commented Nov 18, 2014 at 16:10
The number of rows retrieved by a cursor is not known until you fetch the last row. — mustaccio
– mustaccio, Commented Nov 18, 2014 at 16:19
@mustaccio Yes unfortunately so how can I make the exposed case above without duplicating the SQL logic ? — eka808
– eka808, Commented Nov 18, 2014 at 16:23

score 1 · Accepted Answer · 2014-11-18 18:51:47Z

1

You can do like this, pass a variable to function and function would store amount of rows in it:

function search_foo(param1 varchar2, param1 varchar3, PARAM_ROWCOUNT OUT NUMBER) -- here
  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 [...];

    PARAM_ROWCOUNT := l_return%ROWCOUNT;  -- and here

    return l_return;
}

UPD: Sorry, didn't notice that you want to find out the count of rows before fetching. Unfortunatelly, it might not be possible. But if you want your stored procedure NOT to return too many rows, you can always add a WHERE ROWNUM <= 1000 clause to the query.

Collectives™ on Stack Overflow

Fast way to count rows of a cursor

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related