バルクインサートスクリプト

※コピーすると改行が抜ける場合はソースをダブルクリックして強調表示を外してからコピーしてください

--CREATE TABLE SCOTT.EMP_COPY AS SELECT * FROM SCOTT.EMP WHERE ROWNUM < 0;
--ALTER TABLE SCOTT.EMP_COPY ADD CONSTRAINTS PK_EMP_COPY PRIMARY KEY(EMPNO);

--基本
set serveroutput on
DECLARE
    BULK_NUM CONSTANT PLS_INTEGER := 5; --バルクサイズ
    --CURSOR
    CURSOR CUR_EMP IS
    SELECT * FROM SCOTT.EMP ORDER BY EMPNO;
    --TYPE
    TYPE TYPE_EMP IS TABLE OF CUR_EMP%ROWTYPE INDEX BY BINARY_INTEGER;
    REC_EMP TYPE_EMP;
BEGIN
    OPEN CUR_EMP;
    LOOP
        FETCH CUR_EMP BULK COLLECT INTO REC_EMP LIMIT BULK_NUM;
        EXIT WHEN REC_EMP.COUNT = 0;
        DBMS_OUTPUT.PUT_LINE('フェッチ数:' || REC_EMP.COUNT);
        --バルクインサート
        FORALL i in 1..REC_EMP.COUNT
        INSERT INTO SCOTT.EMP_COPY VALUES REC_EMP(i);
        COMMIT;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('合計フェッチ数:' || CUR_EMP%ROWCOUNT);
    CLOSE CUR_EMP;
end;
/

--エラー発生時、即バルク処理を中断する
set serveroutput on
DECLARE
    BULK_NUM CONSTANT PLS_INTEGER := 5; --バルクサイズ
    --CURSOR
    CURSOR CUR_EMP IS
    SELECT * FROM SCOTT.EMP ORDER BY EMPNO;
    --TYPE
    TYPE TYPE_EMP IS TABLE OF CUR_EMP%ROWTYPE INDEX BY BINARY_INTEGER;
    REC_EMP TYPE_EMP;
BEGIN
    OPEN CUR_EMP;
    LOOP
        FETCH CUR_EMP BULK COLLECT INTO REC_EMP LIMIT BULK_NUM;
        EXIT WHEN REC_EMP.COUNT = 0;
        DBMS_OUTPUT.PUT_LINE('フェッチ数:' || REC_EMP.COUNT);
        BEGIN
            --バルクインサート
            FORALL i in 1..REC_EMP.COUNT
            INSERT INTO SCOTT.EMP_COPY VALUES REC_EMP(i);
            COMMIT;
        EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN  --ORA-00001が発生した場合
                DBMS_OUTPUT.PUT_LINE('エラー発生行 ' || SQL%BULK_EXCEPTIONS(1).ERROR_INDEX);
                DBMS_OUTPUT.PUT_LINE('エラーメッセージ ' ||
                SQLERRM(-SQL%BULK_EXCEPTIONS(1).ERROR_CODE));
                ROLLBACK;
                RAISE;
        END;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('合計フェッチ数:' || CUR_EMP%ROWCOUNT);
    CLOSE CUR_EMP;
end;
/

--エラー発生時もバルク処理は継続する
set serveroutput on
DECLARE
    BULK_NUM CONSTANT PLS_INTEGER := 5; --バルクサイズ
    --CURSOR
    CURSOR CUR_EMP IS
    SELECT * FROM SCOTT.EMP ORDER BY EMPNO;
    --TYPE
    TYPE TYPE_EMP IS TABLE OF CUR_EMP%ROWTYPE INDEX BY BINARY_INTEGER;
    REC_EMP TYPE_EMP;

    --エラー発生回数
    V_ERROR_COUNT PLS_INTEGER := 0;
    --ORA-24381用の例外ハンドラ
    EX_ORA24381 EXCEPTION;
    PRAGMA EXCEPTION_INIT(EX_ORA24381, -24381);
BEGIN
    OPEN CUR_EMP;
    LOOP
        FETCH CUR_EMP BULK COLLECT INTO REC_EMP LIMIT BULK_NUM;
        EXIT WHEN REC_EMP.COUNT = 0;
        DBMS_OUTPUT.PUT_LINE('フェッチ数:' || REC_EMP.COUNT);
        BEGIN
            V_ERROR_COUNT := 0;
            --バルクインサート
            FORALL i in 1..REC_EMP.COUNT SAVE EXCEPTIONS --エラー時も継続
            INSERT INTO SCOTT.EMP_COPY VALUES REC_EMP(i);
            COMMIT;
        EXCEPTION
            WHEN EX_ORA24381 THEN
            V_ERROR_COUNT := V_ERROR_COUNT + SQL%BULK_EXCEPTIONS.COUNT;
            DBMS_OUTPUT.PUT_LINE('エラー件数:' || V_ERROR_COUNT);
            FOR i IN 1..V_ERROR_COUNT LOOP
                DBMS_OUTPUT.PUT_LINE('#' || i || ' エラー発生行 '||
                  SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
                DBMS_OUTPUT.PUT_LINE('エラーメッセージ ' ||
                  SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
            END LOOP;
        END;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('合計フェッチ数:' || CUR_EMP%ROWCOUNT);
    CLOSE CUR_EMP;
end;
/

マニュアル

Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス（10.2）
PL/SQL ユーザーズ・ガイドおよびリファレンス（10.2）