Tuesday, January 9, 2018

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;

/

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect