Mega Code Archive
An exception will stop the bulk insert
<![CDATA[
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 "RNTSOFT"."MYTABLE"."CHAR_COL" (actual: 61, maximum: 60)
PL/SQL procedure successfully completed.
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>
]]>