Home » SQL & PL/SQL » SQL & PL/SQL » How to check row count of sys_refcursor (Oracle11g)
How to check row count of sys_refcursor [message #612238] Tue, 15 April 2014 02:43 Go to next message
na.dharma@gmail.com
Messages: 82
Registered: May 2008
Location: bangalore
Member

How to check row count of sys_refcursor, assume

declare
lv_result sys_refcursor;
begin
open lv_result for select * from emp;

---- here i want ot check the above emp table how may row is returning.

end;
Re: How to check row count of sys_refcursor [message #612239 is a reply to message #612238] Tue, 15 April 2014 03:15 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
With a cursor as you have defined above, I don't see any solution but using %rowcount cursor attribute, but that requires to fetch all rows.

If the purpose is just doing a count, why not simply put the count in a variable? something like:
set serveroutput on
<<bk>>
declare
  cnt pls_integer := 0;
begin
  select
    count(t1.employee_id)
  into
    bk.cnt
  from
    hr.employees t1;
--
  sys.dbms_output.put_line('Number of rows: ' || to_char(bk.cnt));
end;
/
set serveroutput off
Re: How to check row count of sys_refcursor [message #612241 is a reply to message #612238] Tue, 15 April 2014 04:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You cannot know the number of rows in a ref cursor without fetching them.

Re: How to check row count of sys_refcursor [message #612244 is a reply to message #612238] Tue, 15 April 2014 04:37 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
If you fetch the rows into a collection, then you could simply use COUNT to get the count of rows fetched.

SQL> set serveroutput on;
SQL>
SQL> DECLARE
  2     LV_RESULT SYS_REFCURSOR;
  3     TYPE EMP_TYP IS TABLE OF EMP%ROWTYPE;
  4     VAR_EMP EMP_TYP;
  5     CNT     NUMBER;
  6  BEGIN
  7     OPEN LV_RESULT FOR
  8        SELECT * FROM EMP;
  9     FETCH LV_RESULT BULK COLLECT
 10        INTO VAR_EMP;
 11     CLOSE LV_RESULT;
 12     CNT := VAR_EMP.COUNT;
 13     DBMS_OUTPUT.PUT_LINE('The employee count is :' || CNT);
 14  END;
 15  /
The employee count is :10
PL/SQL procedure successfully completed

SQL> set serveroutput off;
SQL>
Re: How to check row count of sys_refcursor [message #612249 is a reply to message #612244] Tue, 15 April 2014 05:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

But take care you have not so much rows returned by your cursor because you then store them in your PGA and consume much memory space which will be no more available for others, without speaking about "ORA-04030: out of process memory when trying to allocate xx bytes" error.

Re: How to check row count of sys_refcursor [message #612258 is a reply to message #612249] Tue, 15 April 2014 06:19 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Thats true Micheal, may be LIMIT parameter should help for it.

Regards,
Pointers
Re: How to check row count of sys_refcursor [message #612260 is a reply to message #612258] Tue, 15 April 2014 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

But in this case you have not the total number of the rows (or you loop and lose part of the cursor rows).

[Updated on: Tue, 15 April 2014 06:30]

Report message to a moderator

Re: How to check row count of sys_refcursor [message #612261 is a reply to message #612258] Tue, 15 April 2014 06:31 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
I agree Michel. My example just had 10 rows so I didn't bother about the context switches. LIMIT(as already mentioned by Pointers) should be used to minimize the number of context switches.

[Michel, saw your above message just now, we posted almost at same time]

[Updated on: Tue, 15 April 2014 06:34]

Report message to a moderator

Re: How to check row count of sys_refcursor [message #612262 is a reply to message #612261] Tue, 15 April 2014 06:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't understand your point. Without LIMIT you have only one context switch, with LIMIT you have many context switches (as many as the number of times you loop).

But, anyway, this (context switch) is not the point of this topic which is, I remind, to get the number of rows returned by a cursor (without losing these rows).

Re: How to check row count of sys_refcursor [message #612265 is a reply to message #612262] Tue, 15 April 2014 07:58 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Micheal,

I read the below article recently, it answers both the above points

1. How to handle missing cursor records with BULK COLLECT using LIMIT
(under the heading "Kicking the %NOTFOUND Habit")
2. LIMIT with context switches
(under the heading "Best practices for knowing your LIMIT and kicking %NOTFOUND")

http://www.oracle.com/technetwork/issue-archive/2008/08-mar/o28plsql-095155.html

Regards,
Pointers
Re: How to check row count of sys_refcursor [message #612268 is a reply to message #612265] Tue, 15 April 2014 08:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1. This is just the consequence of not thinking, you just have to move the test just before the outer "END LOOP" and not just after "FETCH" to make it work as well (and I'm pretty sure SF does it in some of his articles).
2. I don't see where he talk about context switch.

In conclusion, I don't see your point relatively to the question here.

Re: How to check row count of sys_refcursor [message #612270 is a reply to message #612268] Tue, 15 April 2014 08:38 Go to previous message
pointers
Messages: 451
Registered: May 2008
Senior Member
Micheal Laughing
Quote:

But in this case you have not the total number of the rows (or you loop and lose part of the cursor rows).


The idea was just to show a method (there may be many methods possible or correction of a method as you pointed above) to the point you mentioned


Previous Topic: how to remove columns not ending with leaf nodes in hierarchical queries
Next Topic: ORA-29283: invalid file operation
Goto Forum:
  


Current Time: Thu May 09 14:44:04 CDT 2024