"BULK COLLECT" in Oracle runs into an infinite loop

Question

I created the Package/Body as below in the SCOTT in Oracle.

-- PACKAGE SPECIFICATION:

create or replace package emp_pkg Is

    TYPE tbl_emp IS TABLE OF EMP%ROWTYPE;

    PROCEDURE p_displayEmpName;
   
end emp_pkg;

-- PACKAGE BODY:

create or replace package body emp_pkg Is
   
PROCEDURE p_displayEmpName IS   
    CURSOR c_rec IS select * from emp where deptno = 30;
    v_tbl_emp tbl_emp;
    BEGIN
        open c_rec;
        loop
            fetch c_rec bulk collect into v_tbl_emp;
            for i in 1..v_tbl_emp.count loop
                dbms_output.put_line(v_tbl_emp(i).ename || ','||v_tbl_emp(i).hiredate);
            end loop;
        end loop;
    END p_displayEmpName;
end emp_pkg;   

I have no problems compiling the package and body.

Once I executed the procedure, it runs into an infinite loop:

-- CALL ing the procedure:
exec emp_pkg.p_displayempname;

How can I figure out what I did wrong?

Answer 1

EXIT the loop when there are no more rows and CLOSE your cursor (yes, it should be implicitly closed but its better to get into good habits and always close it for when you work with a language/driver where the cursors aren't implicitly closed):

create or replace package body emp_pkg Is
   
PROCEDURE p_displayEmpName
IS   
  CURSOR c_rec IS select * from emp where deptno = 30;
  v_tbl_emp tbl_emp;
  BEGIN
    open c_rec;
    loop
      EXIT WHEN c_rec%NOTFOUND;
      fetch c_rec bulk collect into v_tbl_emp LIMIT 50;

      for i in 1..v_tbl_emp.count loop
        dbms_output.put_line(v_tbl_emp(i).ename || ',' || v_tbl_emp(i).hiredate);
      end loop;
    end loop;

    CLOSE c_rec;
  END p_displayEmpName;
end emp_pkg;
/

db<>fiddle here