DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: URGENT - Bulk Collect with REF CURSOR

  1. #1
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    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.

  2. #2
    Join Date
    Dec 2001
    Location
    Bangalore, India
    Posts
    23

    Smile

    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.

  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    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.

  4. #4
    Join Date
    Dec 2001
    Location
    Bangalore, India
    Posts
    23

    Smile

    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
  •  


Click Here to Expand Forum to Full Width