title: How to continue while loop after exception raised in SQL
body: <p>How would I continue the while loop after an exception has been raised in the code below? </p>
  
  <pre><code>DECLARE    
    v_blob_data       BLOB;    
    v_blob_len        NUMBER;    
    v_position        NUMBER;    
    v_raw_chunk       RAW(10000);    
    v_char      CHAR(1);    
    c_chunk_len   number       := 1;    
    v_line        VARCHAR2 (32767)        := NULL;    
    v_data_array      wwv_flow_global.vc_arr2;    
    v_rows number;    
    v_sr_no number := 1;  
    v_first_line_done boolean := false;  
    v_error_cd number :=0;  
    v_quote_pos1 NUMBER;  
    v_quote_pos2 NUMBER;  
    v_enclosed_str VARCHAR(200);
    v_errmsg VARCHAR2(4000);
  BEGIN
    delete from TEMP_MM_UPDATE where username = :P1_USER_ID;
  
    -- Read data from wwv_flow_files&lt;/span&gt;    
    select    
      blob_content    
      into v_blob_data    
      from wwv_flow_files    
      where name = :P2_FILE_UPLOAD; 
  
    v_blob_len := dbms_lob.getlength(v_blob_data);    
    v_position := 1; 
  
    -- Read and convert binary to char&lt;/span&gt;  
    WHILE ( v_position &lt;= v_blob_len )    
    LOOP 
      begin  
          v_raw_chunk := dbms_lob.substr(v_blob_data,c_chunk_len,v_position);    
          v_char :=  chr(hex_to_decimal(rawtohex(v_raw_chunk)));    
          v_line := v_line || v_char;    
          v_position := v_position + c_chunk_len;
  
        -- When a whole line is retrieved &lt;/span&gt;   
        IF v_char = CHR(10) THEN
          LOOP  
            --Make sure there's something to replace  
            IF INSTR(v_line, '"', 1, 1) = 0 THEN  
              EXIT; -- If nothing to replace, exit loop and don't try  
            END IF;  
            --Find the position of the first and second quotes in the line of text  
            v_quote_pos1 := INSTR(v_line, '"', 1, 1);  
            v_quote_pos2 := INSTR(v_line, '"', 1, 2);  
            --Extract the inner string  
            v_enclosed_str := SUBSTR(v_line, v_quote_pos1 + 1, v_quote_pos2 - v_quote_pos1 - 1);  
            --perform the replacement  
            v_line := SUBSTR(v_line, 0, v_quote_pos1 - 1) || REPLACE(v_enclosed_str, ',', '&lt;') || SUBSTR(v_line, v_quote_pos2 + 1);  
          END LOOP; 
  
          -- Convert comma to : to use wwv_flow_utilities &lt;/span&gt;  
          v_line := REPLACE (v_line, ',', ':');  
          v_line := REPLACE (v_line, '&lt;', ',');  
          v_line := REPLACE (trim(v_line), '-', NULL);  
          --v_line := REPLACE (trim(v_line), '"', NULL);  
          -- Convert each column separated by : into array of data &lt;/span&gt;    
          v_data_array := wwv_flow_utilities.string_to_table (v_line);  
          --Check to see if the row of column headers has already been parsed through  
          IF(v_first_line_done != true)THEN   
            v_first_line_done := true;  
            --Check column order in spreadsheet  
            IF(v_data_array(1)    LIKE '%Username%' AND
                v_data_array(2)  LIKE '%NDN%' AND
                v_data_array(3)  LIKE '%PCFN%') THEN   
              v_error_cd := 0;  
              v_line := NULL;  
            ELSE  
              v_error_cd := 1;  
            END IF;  
          --If first line is done and the column order is correct then  
          ELSIF(v_first_line_done = true AND v_error_cd = 0) THEN   
            -- Insert data into target table &lt;/span&gt;    
            EXECUTE IMMEDIATE 'insert into TEMP_MM_UPDATE   
            (USERNAME,
             RPT_FLAG,
             PCFN)
            values (:1,:2,:3)'   
             USING   
              v_data_array(1),   
              v_data_array(2),   
              v_data_array(3);    
             -- Clear out    
              v_line := NULL; v_sr_no := v_sr_no + 1; 
          END IF;  
        END IF;
      exception
        WHEN OTHERS then
          v_errmsg := SQLERRM;
          insert into temp_mm_update (username,error_desc)
          values (:P1_USER_ID, v_errmsg);
    END;
    END LOOP;
  
    DELETE FROM WWV_FLOW_FILES where name = :P2_FILE_UPLOAD;
    DELETE FROM TEMP_MM_UPDATE WHERE USERNAME IS NULL AND PCFN IS NULL;  
    IF(v_error_cd = 1) THEN  
      INSERT INTO temp_mm_update (USERNAME, ERROR_DESC)  
      VALUES (:P1_USER_ID, 'Error. Please check column order in spreadsheet.');  
    END IF;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      insert into temp_mm_update (username,error_desc)
      values (:P1_USER_ID, 'No Data Found.');
    WHEN OTHERS then
      v_errmsg := SQLERRM;
      insert into temp_mm_update (username,error_desc)
      values (:P1_USER_ID, v_errmsg);
  END;
  </code></pre>
  
  <p>When I put the exception inside the loop like above, the procedure never seems to finish and I end up getting a "NOWAIT" error when I try to delete the table or anything like that.</p>
  
  <p>The code works fine if I take out the "BEGIN" right after the loop and also take out the exception inside the loop, but I want to be able to specify what is wrong with every record instead  of processing correct records and then stopping after it comes to a record that has, for example, 9 values in a column that only accepts 6.</p>
  
  <p>Can anyone help with this?</p>
  
  <p>Thanks,</p>
  
  <p>Steven</p>
  
  <p>EDITS: <br />
  I changed the line: <br />
      ELSIF(v_first_line_done = true AND v_error_cd = 0) <br />
  to<br />
       ELSIF(v_first_line_done = true AND v_error_cd = 0 AND (v_data_array(1) is not null OR v_data_array(1) not like '%-%'))<br />
  and the procedure finishes, but when it comes to a record that raises and exception, all of the following records have the same exception.</p>
  
  <p>Sample of my CSV File:<br />
  Username    NDN PCFN<br />
  DOOL0008    X   808461<br />
  DOOL0008    X   886332<br />
  DOOL0008    X   887223<br />
  DOOL0008    X   887992<br />
  DOOL0008    X   904959<br />
  DOOL0008    X   904959<br />
  DOOL0008    X   904968<br />
  DOOL0008    X   904968<br /></p>
  
  <p>So for example if the column MM (which had a datatype of VARCHAR2(1)) has a value of "XX" on the third record, and exception will be raised that the value is too large, and then for records 4-8 the same exception will be raised.</p>