bulk exception

--opt build 시에 발생
drop table t1;
create table t1 (c1 number, c2 varchar(10));
set serveroutput on;
DECLARE
  TYPE NumTab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  TYPE NameTab IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
  nums  NumTab;
  names NameTab;

  error_message VARCHAR2(100);
  bad_stmt_no   PLS_INTEGER;
  dml_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(dml_errors, -15231);

BEGIN

FOR i IN 1..11 LOOP
  nums(i) := 12-i;
  names(i) := 'Name' || TO_CHAR(12-i);
END LOOP;

FORALL i IN 1..12 SAVE EXCEPTIONS
  INSERT INTO T1 VALUES(nums(i), names(i));
EXCEPTION
  WHEN dml_errors THEN
    FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
      error_message := SQLERRM(-(SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
      DBMS_OUTPUT.PUT_LINE (error_message);
      bad_stmt_no := SQL%BULK_EXCEPTIONS(i).ERROR_INDEX;
      DBMS_OUTPUT.PUT_LINE (bad_stmt_no);
    END LOOP;
END;
/

 

BULK INSERT

데이터베이스에 대량의 데이터를 효율적으로 삽입하기 위한 SQL 명령어 또는 기법입니다. 이 기법은 데이터 삽입 속도를 크게 향상시킬 수 있으며, 특히 많은 양의 데이터를 다루는 경우에 유용합니다.

 

특정 예외 처리

• WHEN dml_errors THEN: dml_errors 예외가 발생했을 때 실행할 코드를 지정합니다. 이 예외는 대량 삽입(FORALL) 도중 발생하는 예외입니다. PRAGMA EXCEPTION_INIT을 사용하여 사용자 정의 예외로 초기화합니다.

예외 정보 반복 처리

• FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP: SQL%BULK_EXCEPTIONS 컬렉션에 저장된 예외들을 반복 처리합니다. SQL%BULK_EXCEPTIONS는 대량 삽입 중 발생한 각 예외의 정보가 담겨 있는 컬렉션입니다.

예외 정보 출력

• error_message := SQLERRM(-(SQL%BULK_EXCEPTIONS(i).ERROR_CODE)): 현재 예외의 오류 코드를 가져와서 그에 해당하는 오류 메시지를 error_message 변수에 저장합니다. SQLERRM 함수는 주어진 오류 코드에 해당하는 메시지를 반환합니다.
• DBMS_OUTPUT.PUT_LINE (error_message): DBMS_OUTPUT.PUT_LINE 프로시저를 사용하여 오류 메시지를 출력합니다.
• bad_stmt_no := SQL%BULK_EXCEPTIONS(i).ERROR_INDEX: 현재 예외가 발생한 삽입 작업의 인덱스를 bad_stmt_no 변수에 저장합니다. ERROR_INDEX는 예외가 발생한 FORALL 반복문의 인덱스를 나타냅니다.
• DBMS_OUTPUT.PUT_LINE (bad_stmt_no): DBMS_OUTPUT.PUT_LINE 프로시저를 사용하여 예외가 발생한 인덱스를 출력합니다.

SQLERRM는 Oracle PL/SQL에서 오류 메시지를 반환하는 함수입니다.

 

 

• 컬렉션 초기화:이 부분에서 nums와 names 컬렉션은 1부터 11까지의 인덱스를 가지며 값이 할당됩니다.

FOR i IN 1..11 LOOP
  nums(i) := 12-i;
  names(i) := 'Name' || TO_CHAR(12-i);
  DBMS_OUTPUT.PUT_LINE ('i='||i);
END LOOP;

• FORALL 구문:여기서 FORALL 구문은 1부터 12까지의 인덱스를 사용하여 삽입 작업을 시도합니다. 그러나 nums(12)와 names(12)는 존재하지 않기 때문에 에러가 발생합니다.
•  

FORALL i IN 1..12 SAVE EXCEPTIONS
  INSERT INTO T1 VALUES(nums(i), names(i));

 

 i=1
i=2
i=3
i=4
i=5
i=6
i=7
i=8
i=9
i=10
i=11
TBR-15104: No matching data found.
11

PSM completed.

SQL> 
SQL> 
SQL> select * from t1;

        C1 C2        
---------- ----------
        11 Name11
        10 Name10
         9 Name9
         8 Name8
         7 Name7
         6 Name6
         5 Name5
         4 Name4
         3 Name3
         2 Name2

10 rows selected.

 

 

error_index가 0부터 count 해서 11