Bulk collect , For all, save exception,Bulk Row count

Bulk collect , For all, save exception

PL/SQL anonymous block doing row-by-row INSERT 

SQL> CREATE TABLE sales_target_errors
  2  (sql_err_mesg varchar2(4000))
  3  /

SQL>  DECLARE
  2        TYPE array IS TABLE OF sales_target%ROWTYPE
  3           INDEX BY BINARY_INTEGER;
  4        sales_src_arr   ARRAY;
  5        errors          NUMBER;
  6        error_mesg     VARCHAR2(255);
  7        bulk_error      EXCEPTION;
  8        l_cnt           NUMBER := 0;
  9        PRAGMA exception_init
 10              (bulk_error, -24381);
 11        CURSOR c IS 
 12           SELECT * 
 13           FROM   sales_src;
 14        BEGIN
 15        OPEN c;
 16        LOOP
 17          FETCH c 
 18             BULK COLLECT 
 19             INTO sales_src_arr 
 20             LIMIT 100;
 21          BEGIN
 22             FORALL i IN 1 .. sales_src_arr.count 
 23                      SAVE EXCEPTIONS
 24               INSERT INTO sales_target VALUES sales_src_arr(i);
 25          EXCEPTION
 26          WHEN bulk_error THEN
 27            errors := 
 28               SQL%BULK_EXCEPTIONS.COUNT;
 29            l_cnt := l_cnt + errors;
 30            FOR i IN 1..errors LOOP
 31              error_mesg := SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
 32              INSERT INTO sales_target_errors 
 33              VALUES     (error_mesg);
 34       END LOOP;
 35          END;
 36          EXIT WHEN c%NOTFOUND;
 37      
 38       END LOOP;
 39       CLOSE c;
 40       DBMS_OUTPUT.PUT_LINE
 41        ( l_cnt || ' total errors' );
 42       END;

-------------------------------------------------------------------------------

2)

SQL%BULK_ROWCOUNT and SQL%BULK_EXCEPTIONS





SQL%BULK_ROWCOUNT and SQL%BULK_EXCEPTIONS are used in bulk opeartions with FORALL

SQL%BULK_ROWCOUNT
Let say, we are processing a bulk operation, need to know how many records are processed by each interation for the given DML (Update, Delete and Insert).

We have SQL%BULK_ROWCOUNT attribute to know the number of records processed in each iteration in a bulk operation.

The follwoing example shows how to use it.

ORCL> create table t_emp2 as select * from emp
  2  /

Table created.

ORCL> select deptno, count(*)
  2     from t_emp2
  3     where deptno in (10, 30)
  4     group by deptno
  5  /

    DEPTNO   COUNT(*)
---------- ----------
        10          3
        30          6

Dept 30 have 6 rows and dept 10 have 3 rows.

ORCL> declare
  2  
  3     type lt_dept_arr is table of number;
  4     l_dept_arr lt_dept_arr := lt_dept_arr(10, 30);
  5  
  6  begin
  7  
  8     forall i in l_dept_arr.first..l_dept_arr.last
  9     delete from t_emp2 where deptno = l_dept_arr(i);
 10  
 11     for j in l_dept_arr.first..l_dept_arr.last loop
 12        dbms_output.put_line ('The number of records deleted in '
 13                               || j || 'th iteration are  '
 14                               ||sql%bulk_rowcount(j) );
 15     end loop;
 16  
 17  end;
/
The number of records deleted in 1th iteration are  3
The number of records deleted in 2th iteration are  6

PL/SQL procedure successfully completed.


Handling exceptions in FORAll using SQL%BULK_EXCEPTIONS
PL/SQL provides a mechanism to handle exceptions that were raised during the FORALL bulk operation using SQL%BULK_EXCEPTIONS.

SQL%BULK_EXCEPTIONS argument saves all the exceptions raised during the bulk operation provided FORALL statement should have SAVE EXCEPTIONS added.

SQL%BULK_EXCEPTIONS.count gives the number of exceptions raised in the last FORALL bulk opearation

SQL%BULK_EXCEPTIONS.ERROR_INDEX gives the iteration number in which the exception is raised

SQL%BULK_EXCEPTIONS.ERROR_CODE gives the error code, using which we can find the error message as ERRM( - SQL%BULK_EXCEPTIONS ). noted that '-' is added at the beginning
of the SQL%BULK_EXCEPTIONS in the SQLERRM function.

Lets take an example to illustrate the functionality.

srikar@ORCL> create table t_emp as select * from emp;

Table created.

We have created a table 't_emp' whice is similar to emp table.

Now, we append each empname with ' Oracl' due to which error is raised in the bulk operation as the empname column maximum size is 10 characters and the append is not allowed for few columns,so the records whose length is crossing 10 characters would raise an exception.

In the follwoing code, if an exception is raised then the exception is saved and processed with the next records.


ORCL> ed
Wrote file afiedt.buf

  1  declare
  2         type lt_emp_arr is table of t_emp.empno%type;
  3         l_emp_arr    lt_emp_arr;
  4         errors       number;
  5  begin
  6         select empno bulk collect into l_emp_arr from t_emp;
  7         forall i in l_emp_arr.first..l_emp_arr.last save exceptions
  8         update t_emp set ename = ename||' Oracl' where empno = l_emp_arr(i) ;
  9  exception
 10       when others then
 11        errors := sql%bulk_exceptions.count;
 12        dbms_output.put_line ('The total number of errors occured are '
 13                                  || errors
 14                             );
 15        for j in 1..errors loop
 16           dbms_output.put_line ('The error iteration is  '
 17                                  || sql%bulk_exceptions(j).error_index
 18                                  || ' and the error code is '
 19                                  || sql%bulk_exceptions(j).error_code
 20                                  || ' and the error message is '
 21                                  || sqlerrm (- sql%bulk_exceptions(j).error_code)
 22                                );
 23        end loop;
 24* end;
 25  /
The total number of errors occured are 11
The error iteration is  1 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  2 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  4 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  5 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  6 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  7 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  8 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  10 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , m
aximum: )
The error iteration is  11 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , m
aximum: )
The error iteration is  12 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , m
aximum: )
The error iteration is  14 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , m
aximum: )

PL/SQL procedure successfully completed.

ORCL> select ename from t_emp;

ENAME
----------
SMITH
ALLEN
WARD Oracl
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING Oracl
TURNER
ADAMS
JAMES
FORD Oracl
MILLER

14 rows selected.