title: insert record into table on exception
body: <p>Here is my scenario:</p>
  
  <p>I want to write a procedure in oracle, there are four tables, tab1, tab2, tab3, err. tab1 has some data in two columns <code>(id number, name varchar(250))</code>, while others are empty.</p>
  
  <p>schema for tab2 is </p>
  
  <p><code>(id number, name varchar(50)), for tab3 (id number, name varchar(250))</code>.</p>
  
  <p>I want to insert data from tab1 to tab2 and when there is exception like name is greater than varchar(50), it will insert into tab3 and also insert error message into err table.</p>
  
  <p>So all record from tab1 should be inserted into tab2 and tab3 accordingly using exception handling.</p>
  
  <p>Here is what I tried but failed.</p>
  
  <pre><code>CREATE OR REPLACE
  PROCEDURE exception_handler
  IS
    vSqlErr  VARCHAR2(200) ;
    vSqlCode VARCHAR2(5) ;
    id2      NUMBER;
    name2    VARCHAR(250);
  BEGIN
    INSERT ALL INTO tab3 VALUES
      (id, name
      )
    SELECT * FROM tab1 t;
  EXCEPTION
  WHEN OTHERS THEN
    INSERT INTO tab2 VALUES
      (id, name
      );
    vSqlErr  := SUBSTR(sqlerrm, 1, 200) ;
    vSqlCode := SUBSTR(SQLCODE, 1, 5) ;
    INSERT INTO err VALUES
      (vSqlErr, vSqlCode
      ) ;
    COMMIT ;
    RAISE;
  END;
  </code></pre>