How To Continue While Loop After Exception Raised In Sql

How would I continue the while loop after an exception has been raised in the code below? DECLARE v_blob_data BLOB; v_blob_len NUMBER; v_position

Solution 1:

By playing around with my code I figured out what was wrong. I needed to add in the following line into my exception block of code:

v_line := NULL; v_sr_no := v_sr_no + 1; 

Final 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);
BEGINdeletefrom TEMP_MM_UPDATE where username = :P1_USER_ID;

  -- Read data from wwv_flow_files</span>    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</span>  
  WHILE ( v_position <= 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 </span>   
      IF v_char = CHR(10) THEN
        LOOP  
          --Make sure there's something to replace  
          IF INSTR(v_line, '"', 1, 1) =0THEN  
            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, ',', '<') || SUBSTR(v_line, v_quote_pos2 +1);  
        END LOOP; 

        -- Convert comma to : to use wwv_flow_utilities </span>  
        v_line := REPLACE (v_line, ',', ':');  
        v_line := REPLACE (v_line, '<', ',');  
        v_line := REPLACE (trim(v_line), '-', NULL);  
        --v_line := REPLACE (trim(v_line), '"', NULL);  -- Convert each column separated by : into array of data </span>    
        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 =trueAND v_error_cd =0) THEN-- Insert data into target table </span>    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;
        insertinto temp_mm_update (username,error_desc)
        values (:P1_USER_ID, v_errmsg);
v_line :=NULL; v_sr_no := v_sr_no +1; 
  END;
  END LOOP;

  DELETEFROM WWV_FLOW_FILES where name = :P2_FILE_UPLOAD;
  DELETEFROM TEMP_MM_UPDATE WHERE USERNAME ISNULLAND PCFN ISNULL;  
  IF(v_error_cd =1) THENINSERTINTO temp_mm_update (USERNAME, ERROR_DESC)  
    VALUES (:P1_USER_ID, 'Error. Please check column order in spreadsheet.');  
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THENinsertinto temp_mm_update (username,error_desc)
    values (:P1_USER_ID, 'No Data Found.');
  WHEN OTHERS then
    v_errmsg := SQLERRM;
    insertinto temp_mm_update (username,error_desc)
    values (:P1_USER_ID, v_errmsg);
END;