Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    16

    Unanswered: how to get count of rows in cursor???

    Is there some fast way to get the count of rows in a cursor?


    I know I could always just do a SELECT count(*) in a separate query, but I'm
    asking in case a cursor has this information already and I just don't know how
    to get it.
    Jason B. Simms

  2. #2
    Join Date
    Jan 2004
    Posts
    492

    Re: how to get count of rows in cursor???

    Originally posted by bosshog33
    Is there some fast way to get the count of rows in a cursor?


    I know I could always just do a SELECT count(*) in a separate query, but I'm
    asking in case a cursor has this information already and I just don't know how
    to get it.

    This is how I generally do it:
    Code:
    Declare
    v_rec_count number:=0;
    
    cursor c_test is
    select ename, empno
    from emp;
    
    Begin
    For r_test in c_test loop
    v_rec_count := v_rec_count + 1;
    dbms_output.put_line ('Record: ' || v_rec_count || ' ' || r_test.ename);
    end loop;
    
    end;

    Most important part is to initialize v_rec_count to 0 in the declare, or else you wont get the correct count.

  3. #3
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    If your cursor already has this information than, it is very easy to retrive it. For e.g,

    DECLARE
    CURSOR c_count IS
    SELECT count (*) total
    FROM <tablename>;
    BEGIN
    FOR v_count IN c_count LOOP
    DBMS_OUTPUT.PUT_LINE (v_count.total);
    END LOOP;
    END;
    /
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  4. #4
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Every time u fetch a row from cursor, your cursor%rowcount value will increase.

    you can get how many times you fetched from cursor..

    execute this to get an idea on how it works.

    Code:
    declare
      cursor c1 is select * from tab where rownum <=5;
      l1 c1%rowtype;
    begin
      open c1;
        dbms_output.put_line('-----------');
        dbms_output.put_line(c1%Rowcount);
        dbms_output.put_line('-----------');
        fetch c1 into l1;
        dbms_output.put_line(c1%Rowcount || ' ' || l1.tname);
        fetch c1 into l1;
        dbms_output.put_line(c1%Rowcount || ' ' || l1.tname);
      close c1;
      dbms_output.put_line('-----------');
      for l2 in c1 loop
         dbms_output.put_line(c1%Rowcount || ' ' || l2.tname);
      end loop;
    end;
    Oracle can do wonders !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •