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;
But I couldn't figure out what I did wrong.
Thank you for your help!
CodePudding user response:
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