How to find number of rows in cursor

Question

I would like to find the number of rows in a cursor. Is there a keyword that can help? Using COUNT, we have to write a query. Any help will be greatly appreciated.

Answer 1

You can use %ROWCOUNT attribute of a cursor.

e.g:

DECLARE
  CURSOR lcCursor IS
  SELECT *
    FROM DUAL;
BEGIN
  OPEN lcCursor ;
  DBMS_OUTPUT.PUT_LINE(lcCursor%ROWCOUNT);
  CLOSE lcCursor ;
END;

Answer 2

Here I am trying to count the total number of customers with age greater than 25. So store the result in the cursor first. Then count the size of the cursor inside the function or in the main begin itself.

 DECLARE
        cname customer24.cust_name%type;
        count1 integer :=0;
        CURSOR MORETHAN is
        SELECT cust_name
        FROM customer24
        where age>25;   
    BEGIN
        OPEN MORETHAN;
        LOOP
        FETCH MORETHAN into cname;
        count1:=count1+1;
        EXIT WHEN MORETHAN%notfound;
        END LOOP;
       -- dbms_output.put_line(count1);
        dbms_output.put_line(MORETHAN%ROWCOUNT);
        CLOSE MORETHAN;
    END;

Answer 3

Try this:

print(len(list(cursor)))

Answer 4

There is a possible work around that may be useful/needed because of the overhead of accessing a database server over a network (e.g., when using Ajax calls)

Consider this:

CURSOR c_data IS
SELECT per_first_name , null my_person_count
  FROM person
 UNION
SELECT null as per_first_name , count( distinct per_id ) as my_person_count
  FROM person
 order by my_person_count ;

The first row fetched has the count of records. One MUST add specific columns fetched (the use of the * does not work), and one can add additional filters.

Answer 5

You can use following simple single line code to print cursor count

dbms_output.put_line(TO_CHAR(cur%rowcount));

Answer 6

You can also use BULK COLLECT so that a LOOP is not needed,

DECLARE
    CURSOR c 
    IS   SELECT *
           FROM employee;
    TYPE emp_tab IS TABLE OF c%ROWTYPE INDEX BY BINARY_INTEGER;
    v_emp_tab emp_tab;
BEGIN
    OPEN c;
    FETCH c BULK COLLECT INTO v_emp_tab;
    DBMS_OUTPUT.PUT_LINE(v_emp_tab.COUNT);
    CLOSE c;
END;
/

Edit: changed employee%ROWTYPE to c%ROWTYPE

Answer 7

DECLARE @STRVALUE                   NVARCHAR(MAX),
    @CREATEDDATE                DATETIME,
    @STANTANCEVALUE             NVARCHAR(MAX),
    @COUNT                      INT=0,
    @JOBCODE                    NVARCHAR(50)='JOB00123654',
    @DATE                       DATETIME=GETDATE(),
    @NAME                       NVARCHAR(50)='Ramkumar',
    @JOBID                      INT;

    CREATE TABLE #TempContentSplitValue (ITEMS NVARCHAR(200))

    SELECT  @JOBID = i.Id FROM JobHeader_TBL  i WHERE Id=1201;

    IF EXISTS (SELECT 1 FROM JobHeader_TBL WHERE Id=@JOBID)
    BEGIN
        SELECT @STRVALUE= Description from ContentTemplate_TBL where Id=1 

        INSERT INTO #TempContentSplitValue SELECT * FROM dbo.split(@STRVALUE, '_')

        SET @STRVALUE=''

        DECLARE db_contentcursor CURSOR  FOR  SELECT ITEMS FROM #TempContentSplitValue

        OPEN db_contentcursor

        FETCH NEXT FROM db_contentcursor 
        INTO @STANTANCEVALUE

            WHILE (@@FETCH_STATUS = 0)
            BEGIN

            SET @STRVALUE +=  @STANTANCEVALUE + 'JOB00123654'

            SET @COUNT += 1

            SELECT @COUNT

            FETCH NEXT FROM db_contentcursor INTO @STANTANCEVALUE
            END
            CLOSE db_contentcursor
            DEALLOCATE db_contentcursor

            DROP TABLE #TempContentSplitValue

            SELECT @STRVALUE
    END

Answer 8

You can’t have cursor count at start. For that you need to fetch complete cursor; that is the way get cursor count.

declare
  cursor c2 is select * from dept;
  var c2%rowtype;
  i number :=0;
begin
  open c2;
  loop
    fetch c2 into var;
    exit when c2%NOTFOUND;
    i: = i+1;
  end loop;
  close c2;
dbms_output.put_line('total records in cursor'||i);
end;

Answer 9

I always read that people loop through results. Why not using a count(*)?

An example from my production code:

PROCEDURE DeleteStuff___(paras_ IN Parameters_Type_Rec)
IS
   
   CURSOR findEntries_ IS
      select * from MyTable
      where order_no = paras_.order_no;
      
   counter_ NUMBER;
   CURSOR findEntries_count_ IS
      SELECT COUNT(*) from MyTable
      where order_no = paras_.order_no;
BEGIN
   OPEN findEntries_count_;
   FETCH findEntries_count_ INTO counter_;
   CLOSE findEntries_count_;
   dbms_output.put_line('total records found: '||counter_);
   
   IF (counter_ = 0) THEN
      -- log and leave procedure
      RETURN;
   END IF;
   
   FOR order_rec_ IN findEntries_ LOOP
   EXIT WHEN findEntries_%NOTFOUND OR findEntries_%NOTFOUND IS NULL;
      -- do stuff - i.e. delete a record.
      API_Package.Delete(order_rec_);
   END LOOP;
END DeleteStuff___;

If the query is small, that is my prefered way. In this example, I just want to know (and log) how many entries I'll delete.

p.s. Ignore the three underlines. In IFS, this is used when you want private procedures or functions.

Answer 10

The cursor_variable.%ROWCOUNT is the solution. But its value will be 0 if you check it after opening. You need to loop through all the records, to get the total row count. Example below:

DECLARE 
  cur sys_refcursor;
  cur_rec YOUR_TABLE%rowtype;
BEGIN
  OPEN cur FOR
  SELECT * FROM YOUR_TABLE;

  dbms_output.put_line(cur%rowcount);--returning 0

  LOOP
    FETCH cur INTO cur_rec;  
    EXIT WHEN cur%notfound;
    dbms_output.put_line(cur%rowcount);--will return row number beginning with 1
    dbms_output.put_line(cur_rec.SOME_COLUMN);
  END LOOP;

  dbms_output.put_line('Total Rows: ' || cur%rowcount);--here you will get total row count
END;
/

Answer 11

This should work for you

DECLARE

  CURSOR get_data_ IS 
    SELECT * 
    FROM   table_abc_ 
    WHERE  owner = user_; -- your query
  counter_ NUMBER:= 0;

BEGIN

  FOR data_ IN get_data_ LOOP
    counter_ := counter_ + 1;
  END LOOP;
  dbms_output.put_line (counter_);

END;

Answer 12

You must open the cursor and then fetch and count every row. Nothing else will work.