title: SQL Exception block gets entered though it shouldn't
body: <p>I'm having a procedure in SQL where I'm reading from an input list and inserting into a table and giving back a list of same type, which consists of all records for which there had been some exception while inserting the data into table.
  Below is the code :</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE TEST(i_EXTRACTION_TYPE_LIST IN EXTRACTION_TYPE_LIST, o_EXTRACTION_TYPE_LIST OUT EXTRACTION_TYPE_LIST) AS 
  
  BEGIN
  
    FOR i in 1..i_EXTRACTION_TYPE_LIST.COUNT
      LOOP
        BEGIN
           For Process_Name In(
        Select P.Extt_Process_sid, P.Execute_For_Sub_Portfolio_Ind From Extt_Process P 
        Where 
        P.Extt_Acc_Region_Cd=i_EXTRACTION_TYPE_LIST(i).Extt_Acc_Region_Cd And 
        P.Extt_Time_Period_View_Cd=i_EXTRACTION_TYPE_LIST(i).Extt_Time_Period_View_Cd
        )
          Loop
          BEGIN
          If (i_EXTRACTION_TYPE_LIST(i).extt_SubPortfolio_Ind = 'Y' And Process_Name.Execute_For_Sub_Portfolio_Ind = 'N') = FALSE Then
            Insert Into Extt_Process_Execution Pe(
              Pe.Extt_Process_Exec_Sid,
              Pe.Extt_Process_sid, 
              Pe.Extt_Process_Exec_Start_Ts,
              Pe.Extt_Portfolio_Id,
              Pe.Extt_Process_Exec_Status_Cd,
              Pe.Report_From_Dt,
              Pe.Report_To_Dt,
              Pe.Created_By_Id,
              Pe.Created_Ts,
              UPDATED_BY_ID,
              UPDATED_TS)
              Values(
              Apex_De_Seq.Nextval,
              Process_name.extt_process_sid,
              i_EXTRACTION_TYPE_LIST(i).Extt_Process_Exec_Start_Ts,
              i_EXTRACTION_TYPE_LIST(i).Extt_Portfolio_Id,
              i_EXTRACTION_TYPE_LIST(i).Extt_Process_Exec_Status_Cd,
              i_EXTRACTION_TYPE_LIST(i).Report_From_Dt,
              i_EXTRACTION_TYPE_LIST(i).Report_To_Dt,
              user,
              Systimestamp,
              user,
              Systimestamp
              );
  
          END IF;
          EXCEPTION
              WHEN others then
  
              IF o_EXTRACTION_TYPE_LIST IS NULL THEN
              o_EXTRACTION_TYPE_LIST:=EXTRACTION_TYPE_LIST(i_EXTRACTION_TYPE_LIST(i));
  
              ELSE
  
            o_EXTRACTION_TYPE_LIST.extend();
  
            o_EXTRACTION_TYPE_LIST(o_EXTRACTION_TYPE_LIST.count):=i_EXTRACTION_TYPE_LIST(i);
            END IF;
            DBMS_OUTPUT.PUT_LINE(o_EXTRACTION_TYPE_LIST.count);
          END;
          End Loop;
        END;
        END LOOP;
  END TEST;
  </code></pre>
  
  <p>The DBMS output for this is 1, even though the record i inserted was perfect and wouldn't cause any exception. I believe the way I'm handling the exception or the scoping of the block is messed up. Please help! </p>