-
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;
Cheers!
OraKid.
-
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.
Regards,
Vijay R.
-
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.
Cheers!
OraKid.
-
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.
Regards,
Vijay R.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|