title: Oracle PLSQL - Error handling in UTL_FILE
body: <p>My script as below, it will load a csv file to PRODUCT_TBL and it any error happened during the process, the script will rollback transaction and output an error message, however it does not print out the message when it hit UTL_FILE error, example invalid file operations. Any help are appreciated. Thanks                                                                                                              </p>
  
  <pre><code>DECLARE
    V_error_code NUMBER;
    V_error_message VARCHAR2(255);
    V_ignore_headerlines NUMBER := 1; 
    V_eof BOOLEAN := FALSE;
    F UTL_FILE.FILE_TYPE;
    V_LINE VARCHAR2 (32767);
    V_PRD_ID PRODUCT_TBL.PRD_ID%TYPE;
    V_PATTERN PRODUCT_TBL.PATTERN%TYPE;
    V_REMARK PRODUCT_TBL.REMARK%TYPE;
    V_CREATED_BY PRODUCT_TBL.CREATED_BY%TYPE;
    V_CREATED_DATE PRODUCT_TBL.CREATED_DATE%TYPE;
    V_MODIFIED_BY PRODUCT_TBL.MODIFIED_BY%TYPE;
    V_MODIFIED_DATE PRODUCT_TBL.MODIFIED_DATE%TYPE;
  BEGIN
    F := UTL_FILE.FOPEN ('DATA_DIR', 'PRODUCT_TBLv51.csv', 'R');
    IF V_ignore_headerlines &gt; 0
    THEN
      BEGIN
        FOR i IN 1 .. V_ignore_headerlines
        LOOP
          UTL_FILE.get_line(F, V_LINE);
        END LOOP;
      EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
          V_eof := TRUE;
      END;
    END IF;
  
    WHILE NOT V_eof
    LOOP
      BEGIN
        UTL_FILE.GET_LINE(F, V_LINE, 32767);
        IF V_LINE IS NULL THEN
          EXIT;
        END IF;
        V_PRD_ID := REGEXP_SUBSTR(V_LINE, '([^,\(]*(\([^\)]*\)[^,\(]*)*)(,|$)', 1, 1, 'i', 1);
        V_PATTERN := REGEXP_SUBSTR(V_LINE, '([^,\(]*(\([^\)]*\)[^,\(]*)*)(,|$)', 1, 2, 'i', 1);
        V_REMARK := REGEXP_SUBSTR(V_LINE, '([^,\(]*(\([^\)]*\)[^,\(]*)*)(,|$)', 1, 12, 'i', 1);
        V_CREATED_BY := REGEXP_SUBSTR(V_LINE, '([^,\(]*(\([^\)]*\)[^,\(]*)*)(,|$)', 1, 13, 'i', 1);
        V_CREATED_DATE := REGEXP_SUBSTR(V_LINE, '([^,\(]*(\([^\)]*\)[^,\(]*)*)(,|$)', 1, 14, 'i', 1);
        V_MODIFIED_BY := REGEXP_SUBSTR(V_LINE, '([^,\(]*(\([^\)]*\)[^,\(]*)*)(,|$)', 1, 15, 'i', 1);
        V_MODIFIED_DATE := REGEXP_SUBSTR(V_LINE, '([^,\(]*(\([^\)]*\)[^,\(]*)*)(,|$)', 1, 16, 'i', 1);
        INSERT INTO PRODUCT_TBL   (PRD_ID,PATTERN,REMARK,CREATED_BY,CREATED_DATE,MODIFIED_BY,MODIFIED_DATE) 
          VALUES(V_PRD_ID, V_PATTERN, V_REMARK, V_CREATED_BY, V_CREATED_DATE, V_MODIFIED_BY, V_MODIFIED_DATE);
      EXCEPTION 
        WHEN OTHERS THEN
          ROLLBACK;
          v_error_code := SQLCODE;
          v_error_message := SQLERRM;
          dbms_output.put_line(v_error_code || SQLERRM);
          EXIT;
      END;
    END LOOP;
  
    COMMIT;
    UTL_FILE.FCLOSE(F);
  
  EXCEPTION
    WHEN UTL_FILE.INVALID_OPERATION THEN
       UTL_FILE.FCLOSE(F);
       dbms_output.put_line('File could not be opened or operated on as requested.');
  END;
  /
  </code></pre>