Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Continue loop after exception raised in SQL

DooliusJul 17 2014 — edited Jul 18 2014

How would I continue the while loop in the code below after an exception has been raised?

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</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) = 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, ',', '<') || 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 = true AND 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;

  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;

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.

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.

Can anyone help with this?

Thanks,

Steven

This post has been answered by Doolius on Jul 18 2014
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 15 2014
Added on Jul 17 2014
14 comments
8,647 views