An exception will stop the bulk insert. : FORALL « PL SQL « Oracle PL / SQL






An exception will stop the bulk insert.

  
SQL>
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );

Table created.

SQL>
SQL>
SQL> DECLARE
  2    TYPE t_Strings IS TABLE OF MyTable.char_col%TYPE
  3      INDEX BY BINARY_INTEGER;
  4    TYPE t_Numbers IS TABLE OF MyTable.num_col%TYPE
  5      INDEX BY BINARY_INTEGER;
  6    v_Strings t_Strings;
  7    v_Numbers t_Numbers;
  8  BEGIN
  9    DELETE FROM MyTable;
 10    FOR v_Count IN 1..10 LOOP
 11      v_Strings(v_Count) := '123456789012345678901234567890';
 12      v_Numbers(v_Count) := v_Count;
 13    END LOOP;
 14
 15    FORALL v_Count IN 1..10
 16      INSERT INTO MyTable (num_col, char_col)
 17        VALUES (v_Numbers(v_Count), v_Strings(v_Count));
 18
 19    v_Strings(6) := v_Strings(6) || 'a';
 20
 21    FORALL v_Count IN 1..10
 22      UPDATE MyTable
 23        SET char_col = char_col || v_Strings(v_Count)
 24        WHERE num_col = v_Numbers(v_Count);
 25  EXCEPTION
 26    WHEN OTHERS THEN
 27      DBMS_OUTPUT.PUT_LINE('Got exception: ' || SQLERRM);
 28      COMMIT;
 29  END;
 30  /
Got exception: ORA-12899: value too large for column "JAVA2S"."MYTABLE"."CHAR_COL" (actual: 61, maximum: 60)

PL/SQL procedure successfully completed.

SQL>
SQL> drop table MyTable;

Table dropped.

SQL>

   
  








Related examples in the same category

1.forall from 1 to 50
2.Insert all 1000 elements using a single FORALL statement
3.The SAVE EXCEPTIONS clause will record any exception during the bulk operation, and continue processing.
4.Use a FORALL to move an associative array into a table