075 - [Oracle PL/SQL] Bulk Binding - bulk_exceptions, bulk collect

벌크로 데이터를 입력할때 오류에 대한 자세한 정보를 가져올 수 있는 방법에 대해서 알아보자

각 로우별 발생한 오류를 모두 확인하는 샘플 코드임.

 

drop table ename;
/
create table ename
as select distinct first_name from employees;
/
select first_name  
from ename;
 

declare
    type ename_t  is table of varchar2(100);
    ename_table ename_t:=ename_t();
    c number:=0;
    errors number;
begin
    for i in (select * from ename )
    loop
        c:=c+1;
        ename_table.extend;
        ename_table(c):=i.first_name;
    end loop;
    
    forall i in ename_table.first.. ename_table.last save exceptions
    update ename
    set first_name=first_name||' to be added:)' --14 char
    where first_name=ename_table(i);

    exception 
    when others then
--        errors := sql%rowcount;
        errors := sql%bulk_exceptions.count;
        dbms_output.put_line ('The total number of errors occured are '|| errors);
        for j in 1..errors 
        loop
            dbms_output.put_line ('The error iteration is  '
                                 || sql%bulk_exceptions(j).error_index
                                 || ' and the error code is '
                                 || sql%bulk_exceptions(j).error_code
                                 || ' and the error message is '
                                 || sqlerrm ( -sql%bulk_exceptions(j).error_code)
                                 );
        end loop;

end;
----------------------------------
The total number of errors occured are 29
The error iteration is  10 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , maximum: )
The error iteration is  11 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , maximum: )
The error iteration is  14 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , maximum: )
The error iteration is  18 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , maximum: )
The error iteration is  21 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , maximum: )
The error iteration is  26 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , maximum: )
The error iteration is  28 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , maximum: )
The error iteration is  29 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , maximum: )
The error iteration is  36 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , maximum: )
The error iteration is  37 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , maximum: )
The error iteration is  42 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , maximum: )
The error iteration is  50 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , maximum: )
The error iteration is  52 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , maximum: )
The error iteration is  53 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , maximum: )
The error iteration is  54 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , maximum: )
The error iteration is  56 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , maximum: )
The error iteration is  57 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , maximum: )
The error iteration is  60 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , maximum: )
The error iteration is  63 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , maximum: )
The error iteration is  69 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , maximum: )
The error iteration is  77 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , maximum: )
The error iteration is  80 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , maximum: )
The error iteration is  81 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , maximum: )
The error iteration is  83 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , maximum: )
The error iteration is  85 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , maximum: )
The error iteration is  86 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , maximum: )
The error iteration is  87 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , maximum: )
The error iteration is  90 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , maximum: )
The error iteration is  91 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , maximum: )

 

 

 

• 위의 방법을 조금 개선한 코드 샘플 - bulk collect, 실행결과는 동일해서 생략.

declare
    type ename_t  is table of varchar2(100);
    ename_table ename_t:=ename_t();
    c number:=0;
    errors number;
begin
    /*
    for i in (select * from ename )
    loop
        c:=c+1;
        ename_table.extend;
        ename_table(c):=i.first_name;
    end loop;
    */
    -- for문 대신에 "bulk collect" 을 사용하면 된다.
    select first_name bulk collect into ename_table 
    from ename;

    forall i in ename_table.first.. ename_table.last save exceptions
    update ename
    set first_name=first_name||' to be added:)' --14 char
    where first_name=ename_table(i);

    exception 
    when others then
--        errors := sql%rowcount;
        errors := sql%bulk_exceptions.count;
        dbms_output.put_line ('The total number of errors occured are '|| errors);
        for j in 1..errors 
        loop
            dbms_output.put_line ('The error iteration is  '
                                 || sql%bulk_exceptions(j).error_index
                                 || ' and the error code is '
                                 || sql%bulk_exceptions(j).error_code
                                 || ' and the error message is '
                                 || sqlerrm ( -sql%bulk_exceptions(j).error_code)
                                 );
        end loop;

end;

 

 

 

• nested tables 아닌 경우 샘플, for문 과 bulk collect 비교

drop table ename2;
/
create table ename2
as select employee_id, first_name
from employees;
/
select * from ename2;


-- nested tables 아니므로 초기화/extend 필요없음(개념 정리용)
declare
    type emp_t is table of varchar2(100) index by binary_integer;
    emp_table emp_t;
    c number:=0;
begin
    for i in (select  employee_id, first_name from ename2)
    loop
        c:=c+1;
        emp_table(c):=i.first_name;
    end loop;
    
    for i in emp_table.first..emp_table.last
    loop
    dbms_output.put_line(emp_table(i));
    end loop;

end;
----------------------------
Ellen
Sundar
Mozhe
David
Hermann
...



declare
type emp_t is table of varchar2(100) index by binary_integer;
emp_table emp_t;
begin
    select first_name bulk collect into emp_table
    from ename2;
    
   for i in emp_table.first..emp_table.last
    loop
    dbms_output.put_line(emp_table(i));
    end loop;

end;
----------------------------
Ellen
Sundar
Mozhe
David
Hermann
...