Oracle SQL :UTL_FILE example

SET SERVEROUTPUT ON

DECLARE
  fHandle UTL_FILE.FILE_TYPE;
  vTextIn varchar2(25);
  vTextOut varchar2(25);
BEGIN
  -- Might get INVALID_PATH, INVALID_MODE, or INVALID_OPERATION
  fHandle := UTL_FILE.FOPEN('TEST_DIR','utlout.txt','w');

  IF UTL_FILE.IS_OPEN(fHandle) THEN
    DBMS_OUTPUT.PUT_LINE('File write open');
  ELSE
    DBMS_OUTPUT.PUT_LINE('File write not open');
  END IF;

  vTextIn := 'Hello World';
  vTextOut := 'World Hello';

  -- Might get INVALID_FILEHANDLE, INVALID_OPERATION or WRITE_ERROR
  UTL_FILE.PUT_LINE(fHandle,vTextIn);

  DBMS_OUTPUT.PUT_LINE('Value write: '||vTextIn);

  -- Might get INVALID_FILEHANDLE or WRITE_ERROR
  UTL_FILE.FCLOSE(fHandle);

  -- Might get INVALID_PATH, INVALID_MODE, or INVALID_OPERATION
  fHandle := UTL_FILE.FOPEN('TEST_DIR','utlout.txt','r');

  IF UTL_FILE.IS_OPEN(fHandle) THEN
    DBMS_OUTPUT.PUT_LINE('File read open');
  ELSE
    DBMS_OUTPUT.PUT_LINE('File read not open');
  END IF;

  -- Might get INVALID_FILEHANDLE, INVALID_OPERATION or READ_ERROR
  --   NO_DATA_FOUND or VALUE_ERROR
  UTL_FILE.GET_LINE(fHandle,vTextOut);

  DBMS_OUTPUT.PUT_LINE('Value read: '||vTextOut);

  -- Might get INVALID_FILEHANDLE or WRITE_ERROR
  UTL_FILE.FCLOSE(fHandle);

  DBMS_OUTPUT.PUT_LINE('Successful Completion');
EXCEPTION
  WHEN UTL_FILE.INVALID_PATH THEN
    RAISE_APPLICATION_ERROR(-20100,'Invalid Path');

  WHEN UTL_FILE.INVALID_MODE THEN
    RAISE_APPLICATION_ERROR(-20101,'Invalid Mode');

  WHEN UTL_FILE.INVALID_FILEHANDLE THEN
    RAISE_APPLICATION_ERROR(-20102,'Invalid Filehandle');

  WHEN UTL_FILE.INVALID_OPERATION THEN
    RAISE_APPLICATION_ERROR(-20103,'Invalid Operation -- May signal a file locked by the OS');

  WHEN UTL_FILE.READ_ERROR THEN
    RAISE_APPLICATION_ERROR(-20104,'Read Error');

  WHEN UTL_FILE.WRITE_ERROR THEN
    RAISE_APPLICATION_ERROR(-20105,'Write Error');

  WHEN UTL_FILE.INTERNAL_ERROR THEN
    RAISE_APPLICATION_ERROR(-20106,'Internal Error');

  WHEN UTL_FILE.CHARSETMISMATCH THEN
RAISE_APPLICATION_ERROR(-20107,'Characterset Mismatch error');

  WHEN UTL_FILE.FILE_OPEN THEN
RAISE_APPLICATION_ERROR(-20108,'Cannot perform the operation.File is open');

  WHEN UTL_FILE.INVALID_MAXLINESIZE THEN
RAISE_APPLICATION_ERROR(-20109,'Invalid Max line size');

  WHEN UTL_FILE.INVALID_FILENAME THEN
RAISE_APPLICATION_ERROR(-20110,'Invalid Filename');

  WHEN UTL_FILE.ACCESS_DENIED THEN
RAISE_APPLICATION_ERROR(-20111,'Access Denied');

  WHEN UTL_FILE.INVALID_OFFSET THEN
RAISE_APPLICATION_ERROR(-20112,'Invalid Offset');

  WHEN UTL_FILE.DELETE_FAILED THEN
RAISE_APPLICATION_ERROR(-20113,'File Delete failed');

  WHEN UTL_FILE.RENAME_FAILED THEN
RAISE_APPLICATION_ERROR(-20114,'File Rename failed');

  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20115,'No Data Found');

  WHEN VALUE_ERROR THEN
    RAISE_APPLICATION_ERROR(-20116,'Value Error');

  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20117,'Unknown UTL_FILE Error');
END;