PLSQL Exception handling in GET_FILE

Question

Trying to handle exception if the input file received has invalid records or no data. The below exception runs in loop even though the input file has only 1 record.

LOOP
          BEGIN
               UTL_FILE.GET_LINE(a_UIN_input_file,a_UIN_file);
               a_rec := substr(a_uin_file,1,9);

      EXCEPTION
             WHEN NO_DATA_FOUND THEN
             a_error_file := utl_file.fopen('TAMUOUT','PWT_TEST5_ERROR.txt','w');
             utl_file.put_line(a_error_file,'Bad UIN Read  ' ||SQLERRM||'\n');
             utl_file.fclose(a_error_file);
          -- EXIT;
      END; 
   END LOOP;

         FOR rec IN get_details_4_uin_c(a_rec)  LOOP 
             a_pidm :=fwt_get_pidm_from_uin(a_rec);
             a_bill_hours := fwt_get_enrolled_hours(a_pidm,in_term_code);      
             utl_file.put_line(a_out_file,a_parm,
                               autoflush=>TRUE);
          END LOOP;

   utl_file.fclose(a_uin_input_file);

if i use exit instead of writing into an error file, it works. — arsha, Nov 11 '19 at 22:14
What do you mean "going into loops". PLease edit the question and explain better. — OldProgrammer, Nov 11 '19 at 22:19
My question is correct. This logic keeps writing in the error file without closing the file once a bad record is read. — arsha, Nov 11 '19 at 22:25

score 2 · Accepted Answer · answered Nov 12 '19 at 12:47

UTL_FILE.GET_LINE raises the NO_DATA_FOUND exception when you have reached the end of the file and then keep on trying to read more.

So your exception handler for NO_DATA_FOUND should exit the loop. But within the loop, if you read a line successfully, then run your loop on that.

I believe the code believe will help you get to your solution.

BEGIN
   LOOP
      BEGIN
         UTL_FILE.get_line (a_uin_input_file, a_uin_file);
         a_rec := SUBSTR (a_uin_file, 1, 9);

         FOR rec IN get_details_4_uin_c (a_rec)
         LOOP
            a_pidm := fwt_get_pidm_from_uin (a_rec);
            a_bill_hours := fwt_get_enrolled_hours (a_pidm, in_term_code);
            UTL_FILE.put_line (a_out_file, a_parm, autoflush => TRUE);
         END LOOP;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            UTL_FILE.fclose (a_uin_input_file);
            EXIT;
      END;
   END LOOP;
END;

Thank you. should i be closing the error file (a_error_file) or my input file (a_uin_input_file). — arsha, Nov 12 '19 at 16:24

PLSQL Exception handling in GET_FILE

1 Answers1