CODEWITHSUNDEEP
sqlplsqloracle11g
user1902849
user1902849

Reputation: 1141

Oracle PLSQL - Error handling in UTL_FILE

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

Upvotes: 1

Views: 10621

Answers (1)

Angelo Fuchs
Angelo Fuchs

Reputation: 9941

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.

Upvotes: 1

Related Questions