Hi Balaji,

In your procedure, you are passing the cursor select statement dynamically. Please try the following procedure. Here I’m passing the select statement directly.

declare
TYPE typ_empno IS TABLE OF emp.empno%TYPE INDEX BY BINARY_INTEGER;
TYPE typ_ref is REF CURSOR;
lv_cur_dynamic typ_ref;
lv_typ_empno typ_empno;
lv_qry_str varchar2(30000) := 'SELECT empno FROM emp';
begin
OPEN lv_cur_dynamic for select empno from emp;
-- OPEN lv_cur_dynamic for lv_qry_str;
LOOP
FETCH lv_cur_dynamic bulk collect INTO lv_typ_empno LIMIT 500;
FORALL i IN 1..lv_typ_empno.count
INSERT INTO dummy VALUES (lv_typ_empno(i));
EXIT WHEN lv_cur_dynamic%NOTFOUND;
END LOOP;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('err >>> '||SQLERRM);
END;

Hope this will help you and solve your problem.