Search notes:

Oracle: UTL_FILE

utl_file provides some functions to access data stored in «ordinary» files (i.e. outside of data files) on the filesystem of the server where the instance is running.
Only files in file-system directories for which a corresponding Oracle directory object was created can be accessed with utl_file.

Procedures and functions

fclose
fclose_all
fcopy
fflush
fgetattr
fgetpos
fopen
fopen_nchar
fremove Deletes a file
frename
fseek
get_line Reads a line whose maximum length is 32767 bytes (unless specified smaller in fopen).
get_line_nchar
get_raw Reads a raw string. Unlike get_line, this procedure ignores new lines.
is_open
new_line
put
putf
putf_nchar
put_line
put_line_nchar
put_nchar
put_raw

Examples

Create a file and write three lines into it:
declare
   temp_dir varchar2(266);
begin
   dbms_system.get_env('TEMP', temp_dir);
   execute immediate 'create directory TMP_DIR as ''' || temp_dir || '''';
end;
/

declare
  f   utl_file.file_type;
begin

  f := utl_file.fopen('TMP_DIR', 'utl_file.test', 'W');

  utl_file.put_line(f, 'First line');
  utl_file.put_line(f, 'Second line');
  utl_file.put_line(f, 'Third line');

  utl_file.fclose(f);

end;
/

-- drop directory TMP_DIR;

-- $type %temp%\utl_file.test
Github repository Oracle-Patterns, path: /Installed/utl/file/create_file.plsql
Read the file linewise:
declare

   f     utl_file.file_type;

   line  varchar2(100);

begin

  f := utl_file.fopen('TMP_DIR', 'utl_file.test', 'R');

  utl_file.get_line(f, line); dbms_output.put_line(line);
  utl_file.get_line(f, line); dbms_output.put_line(line);

  utl_file.fclose(f);

end;
/
Github repository Oracle-Patterns, path: /Installed/utl/file/read_created_file.plsql
Read entire file:
declare

   f     utl_file.file_type;

   line  varchar2(100);

begin

  f := utl_file.fopen('TMP_DIR', 'utl_file.test', 'R');

  begin loop

        utl_file.get_line(f, line); 
        dbms_output.put_line(line);

        end loop;

        exception when no_data_found then 
            null; -- Last line read read
  end;

  utl_file.fclose(f);

end;
/
Github repository Oracle-Patterns, path: /Installed/utl/file/read_created_file_until_end.plsql
Delete the file
begin

   utl_file.fremove('TMP_DIR', 'utl_file.test'); 

-- Directory not needed anymore:
   execute immediate 'drop directory TMP_DIR';

end;
/
Github repository Oracle-Patterns, path: /Installed/utl/file/delete_created_file.plsql

See also

The methods dbms_scheduler.get_file and dbms_scheduler.put_file
dbms_file_transfer
Oracle UTL packages

Index