Oracle PLSQL - Error handling in UTL_FILE

Question

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

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

Angelo Fuchs · Accepted Answer · 2013-09-27 18:40:50Z

1

add an EXCEPTION ... OTHER Block after the UTL_FILE part and see what kind of Exceptions actually go throuhg to catch them.

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.');
  WHEN OTHERS THEN
     dbms_output.put_line('other trouble'||SQLCODE||SQLERRM);

When you know which one happened you will know how to catch it.

edited Sep 27, 2013 at 18:40

answered Sep 27, 2013 at 18:35

Angelo Fuchs

9,9911 gold badge39 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Luke Woodward Over a year ago

It's WHEN OTHERS, not WHEN OTHER.

Collectives™ on Stack Overflow

Oracle PLSQL - Error handling in UTL_FILE

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related