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);

Answer 1

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;