UTL_FILE Examples - FOPEN, PUT_LINE, GET_LINE, FCLOSE, FCOPY, FRENAME, FREMOVE

DECLARE

  l_file        UTL_FILE.FILE_TYPE;

  l_file_data    VARCHAR2(2000);

  l_file_name    VARCHAR2(200) := 'File_'||TO_CHAR(SYSDATE,'YYYY_MM_DD_MISS')||'.txt';

  l_file_dir    VARCHAR2(200) := '/xxaa/out/XXAA_FILE';

  l_line_count    NUMBER := 0;

BEGIN

  --> Writing into File

  --> CREATE DIRECTORY XXAA_FILE AS '/xxaa/out/XXAA_FILE';

  l_file := UTL_FILE.FOPEN('XXAA_FILE', l_file_name, 'w');

  UTL_FILE.PUT_LINE(l_file, 'Dummy File');

  UTL_FILE.FCLOSE(l_file);

 

  -->Reading from File

  l_file := UTL_FILE.FOPEN(l_file_dir,l_file_name,'r');

  LOOP

    l_line_count := l_line_count + 1;

    UTL_FILE.GET_LINE(l_file, l_file_data);

    UTL_FILE.FCLOSE(l_file);

  END LOOP;

  --> Copying the file to Archive directory and removing the original file

  l_sysdate := TO_CHAR (SYSDATE,'_MMDDYYYY_HH24MISS');

  UTL_FILE.FCOPY (l_file_dir,l_file_name,l_file_dir||'/Archive',CONCAT(SUBSTR(l_file_name,1,INSTR(l_file_name,'.')-1),l_sysdate||SUBSTR(l_file_name, -4)));

 

  --> Copying the file to Archive directory filename with timestamp 

  UTL_FILE.FRENAME(l_file_dir,l_file_name,l_file_dir||'/Archive', CONCAT(SUBSTR(l_file_name,1,INSTR(l_file_name,'.')-1),l_sysdate||SUBSTR(l_file_name, -4)),TRUE);

  UTL_FILE.FREMOVE (l_file_dir, l_file_name);

 

EXCEPTION

  WHEN UTL_FILE.INVALID_PATH THEN

    UTL_FILE.FCLOSE_ALL;

    RAISE_APPLICATION_ERROR(-20051, 'Invalid Path');

  WHEN UTL_FILE.INVALID_MODE THEN

    UTL_FILE.FCLOSE_ALL;

    RAISE_APPLICATION_ERROR(-20052, 'Invalid Mode');

  WHEN UTL_FILE.INTERNAL_ERROR THEN

    UTL_FILE.FCLOSE_ALL;

    RAISE_APPLICATION_ERROR(-20053, 'Internal Error');

  WHEN UTL_FILE.INVALID_OPERATION THEN

    UTL_FILE.FCLOSE_ALL;

    RAISE_APPLICATION_ERROR(-20054, 'Invalid Operation');

  WHEN UTL_FILE.INVALID_FILEHANDLE THEN

    UTL_FILE.FCLOSE_ALL;

    RAISE_APPLICATION_ERROR(-20055, 'Invalid Operation');

  WHEN UTL_FILE.WRITE_ERROR THEN

    UTL_FILE.FCLOSE_ALL;

    RAISE_APPLICATION_ERROR(-20056, 'Invalid Operation');

END;

/