SQL & PL/SQL

Hi

I am using Bulk Collect with LIMIT clause and FORALL to process some records. The problem is if there are some faulty records
in a batch specified by Limit, all the records are rejected. How can I make sure that only faulty records are rejected and other are processed.

Consider the below example:

Here I am trying to Insert records into a table. Only faulty records should be rejected due to "value larger than specified precision allowed for this column".

SQL> Select * from tab1 order by col1;
 
      COL1
----------
       100
       200
       300
       400
       500
       600
       700
       800
      2000
      3000
      4000
 
11 rows selected
 
SQL> Create table tab2(col number(3)); --Trying to Insert 4 digit records here, which should fail.
 
Table created
 
SQL> 
SQL>  Declare
  2  Cursor c1 is select col1 from tab1 order by col1;
  3  Type num_type is table of number;
  4  b_num num_type;
  5  Begin
  6    Open c1;
  7    Loop
  8    Fetch c1 bulk collect into b_num limit 5;
  9    Forall i in b_num.first .. b_num.last
 10    insert into tab2 (col) values (b_num(i));
 11    commit;
 12    end loop;
 13    End;
 14  /
 
Declare
Cursor c1 is select col1 from tab1 order by col1;
Type num_type is table of number;
b_num num_type;
Begin
  Open c1;
  Loop
  Fetch c1 bulk collect into b_num limit 5;
  Forall i in b_num.first .. b_num.last
  insert into tab2 (col) values (b_num(i));
  commit;
  end loop;
  End;
 
ORA-01438: value larger than specified precision allowed for this column
ORA-06512: at line 10
 
SQL> Select * from tab2;
 
 COL
----
 100
 200
 300
 400
 500
 
SQL>

I want the values 600,700 and 800 also to be inserted in the table as these are valid 3 digit values. How can I do this (Using Bulk and ForAll).

Regards
Abhi