title: Insert in Cursor loop and Exception handling - Oracle
body: <p>I have a table called DUD which is pretty much Static (which means once the data is inserted it never changes). I query data from DUD and populate a staging table CAR from which Webmethods polls everyday.</p>
  
  <p>Usually it is 10 records for <strong>every transaction</strong>. There are two transactions per day.</p>
  
  <p>I have written a Cursor to do this and I am happy with the logic.</p>
  
  <p>The output will look like:</p>
  
  <pre><code>TRANSID   A    B    C   cnt
  ------   ---  ---   --  ---
  A123     JIM  NY   ACT   1
  A123     BOB  CA   ACT   2
  A123     PIN  GA   ACT   3
  --------------------------
  A124     MIK  CA   ACT   1
  A124     JON  MA   ACT   2
  A124     CON  MY   ACT   3
  A124     JIB  CA   ACT   4
  </code></pre>
  
  <p>What really concerns me and question is:</p>
  
  <ol>
  <li><p>If the insert in the loop fails, it should rollback all the inserts made in this transaction and do not end up with partially inserted records or orphaned records for <strong>a transaction</strong>. I commit only after the loop is completed no exception was raised.</p></li>
  <li><p>When exception happens, I also want to know which record failed to insert. I hope to catch this in my exception and call a function in the exception handler that will insert this information in to an Error table for further investigation.</p></li>
  <li><p>The auto commit is disabled in the DB. But will oracle consider ALL the insert through a loop as one transaction or independent transactions and insert it immediately?</p></li>
  </ol>
  
  <p><strong>Code</strong></p>
  
  <pre><code>  DECLARE  TYPE message_info 
    IS 
      RECORD 
      ( 
        message_code INTEGER, 
        message      VARCHAR2(500)); 
      msg MESSAGE_INFO; 
      tranid  NUMBER; 
      p_error EXCEPTION; 
      CURSOR b1 IS 
        SELECT * 
        FROM   dud 
        WHERE  dud.DATE = SYSDATE 
        AND    dud.status='ACTIVE'; 
  
    BEGIN 
      IF *CHECK SOME condition* 
        BEGIN 
          tranid = seq_transid.NEXTVAL; 
          --- Transaction id is unique per transaction. 
          --- All 10 records will have same transaction id. 
          FOR b1 IN c1 
          LOOP 
            i=b1%rowcount; 
            INSERT INTO car 
                        ( 
                                    transid, 
                                    a, 
                                    b, 
                                    c, 
                                    cnt 
                        ) 
                        VALUES 
                        ( 
                                    tranid, 
                                    b1.a, 
                                    b1.b, 
                                    b1.c, 
                                    i 
                        ); 
  
          END LOOP; 
        EXCEPTION 
        WHEN OTHERS THEN 
          ROLLBACK; 
          msg.message := 'Unable to insert into CAR Table'; 
          RAISE p_error; 
        END; 
        COMMIT; 
      EXCEPTION 
      WHEN p_error THEN 
        error.post_msg (msg.message, SQLCODE,SQLERRM,USER); 
      END IF; 
    END;
  </code></pre>