2 Replies Latest reply: Apr 13, 2012 2:44 AM by Kki RSS

    bulk exception error

    Kki
      Hi All,
      my procedure is like this ...

      CREATE OR REPLACE PROCEDURE nested_proc
      IS
      CURSOR cur_TEMPVARRAY
      IS
      SELECT * FROM TEMPVARRAY;

      TYPE typ_TEMPVARRAY IS TABLE OF cur_TEMPVARRAY%ROWTYPE;

      r_TEMPVARRAY typ_TEMPVARRAY;
      --v_TEMPVARRAY TEMPVARRAY%rowtype;
      error_count number;
      dml_error exception;
      pragma exception_init(dml_error,-20902);

      BEGIN
      --pmytab ('inserted-1');

      -- execute immediate 'truncate table cp_tempvarray';
      OPEN cur_TEMPVARRAY;

      LOOP
      FETCH cur_TEMPVARRAY BULK COLLECT INTO r_TEMPVARRAY limit 10;

      FORALL i_tp IN r_TEMPVARRAY.FIRST .. r_TEMPVARRAY.LAST save exceptions
      -- INSERT INTO cp_tempvarray
      -- VALUES r_TEMPVARRAY (i_tp);

      INSERT INTO cp_tempvarray
      VALUES (r_TEMPVARRAY (i_tp).a,
      r_TEMPVARRAY (i_tp).b,
      r_TEMPVARRAY (i_tp).c,
      r_TEMPVARRAY (i_tp).d,
      r_TEMPVARRAY (i_tp).e,
      r_TEMPVARRAY (i_tp).f,
      r_TEMPVARRAY (i_tp).g,
      r_TEMPVARRAY (i_tp).h,
      r_TEMPVARRAY (i_tp).i,
      r_TEMPVARRAY (i_tp).j,
      r_TEMPVARRAY (i_tp).j);

      -- pmytab ('inserted1');

      EXIT WHEN cur_TEMPVARRAY%NOTFOUND;
      END LOOP;
      COMMIT;

      CLOSE cur_TEMPVARRAY;

      COMMIT;

      EXCEPTION

      WHEN DML_ERROR THEN
      error_count :=sql%bulk_exceptions.count;
      DBMS_OUTPUT.put_line('Number of failures: ' || error_count);
      for bulk_err in 1 .. error_count
      loop
      dbms_output.put_line('error' || bulk_err ||
      'array index'||sql%bulk_exceptions(bulk_err).error_index||
      'Massage'||sqlerrm(-sql%bulk_exceptions(bulk_err).error_code));
      end loop;

      END nested_proc;

      when i am executing this procedure, i am getting the below error+


      SQL> execute nested_proc;
      ERROR:
      ORA-24381: error(s) in array DML
      ORA-06512: at "VRESS.NESTED_PROC", line 18
      ORA-06512: at line 1

      can any body help me plzzz..

      Thanks,
      Krupa
        • 1. Re: bulk exception error
          rp0428
          >
          ORA-06512: at "VRESS.NESTED_PROC", line 18
          >
          One or more rows of your FORALL failed and you didn't trap the exception so your code blew up.

          When bulk processing with FORALL you need to trap the bulk exception if you want processing to continue if an error is encountered with one of the records.

          You are trapping AN exception
          pragma exception_init(dml_error,-20902);
          but that is not the bulk exception that you need to trap. You need to trap ORA-24381.
          >
          Example 12-9 shows how you can perform a number of DML operations, without stopping if some operations encounter errors. In the example, EXCEPTION_INIT is used to associate the DML_ERRORS exception with the predefined error ORA-24381. ORA-24381 is raised if any exceptions are caught and saved after a bulk operation.
          >

          See Handling FORALL Exceptions in the PL/SQL Language Reference
          http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/tuning.htm#i49099
          • 2. Re: bulk exception error
            Kki
            Thanks ,i have corrected in my program.

            Thanks once again

            Regards,
            Krupa