UTL_FILE in Oracle Apps

UTL FILE is used to Read/Write data to/from a file. This is mostly used for Outbound Interfaces

Syntax for FOPEN

UTL_FILE.FOPEN (
   location     IN VARCHAR2,
   filename     IN VARCHAR2,
   open_mode    IN VARCHAR2,
   max_linesize IN BINARY_INTEGER) 
  RETURN file_type;

Syntax for PUT

UTL_FILE.PUT (
   file      IN FILE_TYPE,
   buffer    IN VARCHAR2);

Syntax for PUT_LINE

UTL_FILE.PUT_LINE (
   file      IN FILE_TYPE,
   buffer    IN VARCHAR2,
   autoflush IN BOOLEAN DEFAULT FALSE);

Syntax for IS_OPEN

UTL_FILE.IS_OPEN (
   file  IN FILE_TYPE)
  RETURN BOOLEAN;

UTL_FILE  EXCEPTIONS:

1. UTL_FILE.INVALID_OPERATION
2. UTL_FILE.INVALID_PATH
3. UTL_FILE.INVALID_MODE
4. UTL_FILE.INVALID_FILEHANDLE
5. UTL_FILE.READ_ERROR
6. UTL_FILE.INTERNAL_ERROR



Example 1:

SQL> CREATE DIRECTORY log_dir AS '/appl/gl/log'; 
SQL> GRANT READ ON DIRECTORY log_dir TO DBA; 
SQL> GRANT WRITE ON DIRECTORY log_dir TO DBA; 

Example 2:

DECLARE 
  V1 VARCHAR2(32767); 
  F1 UTL_FILE.FILE_TYPE; 
BEGIN 
  -- In this example MAX_LINESIZE is less than GET_LINE's length request 
  -- so the number of bytes returned will be 256 or less if a line terminator is seen. 
  F1 := UTL_FILE.FOPEN('user_dir','u12345.tmp','R',256); 
  UTL_FILE.GET_LINE(F1,V1,32767); 
  UTL_FILE.FCLOSE(F1); 

  -- In this example, FOPEN's MAX_LINESIZE is NULL and defaults to 1024, 
  -- so the number of bytes returned will be 1024 or less if a line terminator is seen. 
  F1 := UTL_FILE.FOPEN('   user_dir','u12345.tmp','R'); 
  UTL_FILE.GET_LINE(F1,V1,32767); 
  UTL_FILE.FCLOSE(F1); 

  -- In this example, GET_LINE doesn't specify a number of bytes, so it defaults to 
  -- the same value as FOPEN's MAX_LINESIZE which is NULL in this case and defaults to 1024. 
  -- So the number of bytes returned will be 1024 or less if a line terminator is seen. 
  F1 := UTL_FILE.FOPEN('user_dir','u12345.tmp','R'); 
  UTL_FILE.GET_LINE(F1,V1); 
  UTL_FILE.FCLOSE(F1); 
END; 

declare
lv_ma_file_path varchar2(100) := 'XXYH_INT_SCILOG_O_093_OUT';
lv_file_name varchar2(100) := 'Testfile.txt';
lv_ma_file_out               UTL_FILE.FILE_TYPE;
begin
lv_ma_file_out := UTL_FILE.fOPEN (lv_ma_file_path, lv_data_file_name, 'W');
 UTL_FILE.PUT_LINE(lv_ma_file_out,'Test file ');
utl_File.fclose(lv_ma_file_out);
end;


declare
  fhandle  utl_file.file_type;
begin
  fhandle := utl_file.fopen(
                'UTL_DIR'     -- File location
              , 'test_file.txt' -- File name
              , 'w' -- Open mode: w = write.
                  );

  utl_file.put(fhandle, 'Hello world!'
                      || CHR(10));
  utl_file.put(fhandle, 'Hello again!');

  utl_file.fclose(fhandle);
exception
  when others then
    dbms_output.put_line('ERROR: ' || SQLCODE
                      || ' - ' || SQLERRM);
    raise;
end;
/