URGENT - Bulk Collect with REF CURSOR

I am getting the following error when I am using BULK COLLECT option with REF CURSOR. Can someone throw some light on why this error is coming?
err >>> ORA-01001: invalid cursor

The procedure smippet is given below:
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 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;

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.

the issue is - this procedure will be gettin the query string from another procedure. hence in this procedure it is not possible to hard-code the query string.

Hi Balaji,

It seems like, you cannot bulk-collect from a cursor into a collection of records.

For more details, please visit the below site:

http://otn.oracle.com/docs/products/...olls.htm#28396

Hope this will help you.