networker networker - 3 months ago 7
SQL Question

counting rows from a cursor in pl/sql

I'm trying to count the number of rows that will be returned from an sql statement . This statement is in a cursor

My code is like this

DECLARE

v_counter int := 0 ;

select count(*) into v_counter from (
cursor get_sth is select * from table1 where condit..) ;


BEGIN

DBMS_OUTPUT.PUT_LINE (v_counter);


END ;
/


and it doesn't work

Is there any other solution that makes me counting the number of rows of a cursor result , I'm really noob

thanks helpers

Answer

What about

DECLARE
     v_counter INT := 0;
BEGIN
     SELECT COUNT(*) INTO v_counter FROM table1 WHERE condit..;
     dbms_output.put_line(v_counter);
END;
/

?

If you already have cursor and just want to know how many records it returns, you have to fetch them all

DECLARE
     CURSOR get_sth IS
          SELECT * FROM table1 WHERE condit..;
     sth       get_sth%ROWTYPE;
     v_counter NUMBER;
BEGIN
     OPEN get_sth;
     LOOP
          FETCH get_sth
               INTO sth;
          EXIT WHEN get_sth%NOTFOUND;
     END LOOP;
     v_counter := get_sth%ROWCOUNT;
     dbms_output.put_line(v_counter);
     CLOSE get_sth;
END;
/