René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

UTL_FILE

The Oracle supplied package UTL_FILE can be used to read and write files that are located on the server. It cannot be used to access files locally, that is on the computer where the client is running.

Procedures/Functions

fclose

procedure fclose (
 file                           in out                   record        
);

fclose_all

procedure fclose_all (
);

fcopy

procedure fcopy (
 src_location                       in                 varchar2        ,
 src_filename                       in                 varchar2        ,
 dest_location                      in                 varchar2        ,
 dest_filename                      in                 varchar2        ,
 start_line                         in           binary_integer default,
 end_line                           in           binary_integer default
);

fflush

procedure fflush (
 file                               in                   record        
);

fgetattr

procedure fgetattr (
 location                           in                 varchar2        ,
 filename                           in                 varchar2        ,
 fexists                           out                  boolean        ,
 file_length                       out                   number        ,
 block_size                        out           binary_integer        
);

fgetpos

function fgetpos returns binary_integer
 (
 file                               in                   record        
);

fopen

function fopen returns record
 (
 location                           in                 varchar2        ,
 filename                           in                 varchar2        ,
 open_mode                          in                 varchar2        ,
 max_linesize                       in           binary_integer default
);
Opens a file. Takes four parameters: location, filename, open_mode and max_linesize
location: must be either an (existing) directory on the server AND be in the utl_file_dir paramter, or
a directory.
open_mode: 'W' for writing access or 'R' for reading access. Additionally, a 'B' can be specified for binary access.

fopen_nchar

function fopen_nchar returns record
 (
 location                           in                 varchar2        ,
 filename                           in                 varchar2        ,
 open_mode                          in                 varchar2        ,
 max_linesize                       in           binary_integer default
);

fremove

procedure fremove (
 location                           in                 varchar2        ,
 filename                           in                 varchar2        
);

frename

procedure frename (
 src_location                       in                 varchar2        ,
 src_filename                       in                 varchar2        ,
 dest_location                      in                 varchar2        ,
 dest_filename                      in                 varchar2        ,
 overwrite                          in                  boolean default
);

fseek

procedure fseek (
 file                           in out                   record        ,
 absolute_offset                    in           binary_integer default,
 relative_offset                    in           binary_integer default
);

get_line

procedure get_line (
 file                               in                   record        ,
 buffer                            out                 varchar2        ,
 len                                in           binary_integer default
);
Reads a line from the opened file.
The maximum line length that can be read is 32K in 9i.

get_line_nchar

procedure get_line_nchar (
 file                               in                   record        ,
 buffer                            out                nvarchar2        ,
 len                                in           binary_integer default
);

get_raw

procedure get_raw (
 file                               in                   record        ,
 buffer                            out                      raw        ,
 len                                in           binary_integer default
);

is_open

function is_open returns boolean
 (
 file                               in                   record        
);

new_line

procedure new_line (
 file                               in                   record        ,
 lines                              in           binary_integer default
);

put

procedure put (
 file                               in                   record        ,
 buffer                             in                 varchar2        
);

putf

procedure putf (
 file                               in                   record        ,
 format                             in                 varchar2        ,
 arg1                               in                 varchar2 default,
 arg2                               in                 varchar2 default,
 arg3                               in                 varchar2 default,
 arg4                               in                 varchar2 default,
 arg5                               in                 varchar2 default
);

putf_nchar

procedure putf_nchar (
 file                               in                   record        ,
 format                             in                nvarchar2        ,
 arg1                               in                nvarchar2 default,
 arg2                               in                nvarchar2 default,
 arg3                               in                nvarchar2 default,
 arg4                               in                nvarchar2 default,
 arg5                               in                nvarchar2 default
);

put_line

procedure put_line (
 file                               in                   record        ,
 buffer                             in                 varchar2        ,
 autoflush                          in                  boolean default
);
Writes a line into the opened file.
If a line was already written, it starts the line with CR/LF. This implies that the file, when being written into, does not end with CR/LF.
The maximum line length that can be written is 32K in 9i.

put_line_nchar

procedure put_line_nchar (
 file                               in                   record        ,
 buffer                             in                nvarchar2        
);

put_nchar

procedure put_nchar (
 file                               in                   record        ,
 buffer                             in                nvarchar2        
);

put_raw

procedure put_raw (
 file                               in                   record        ,
 buffer                             in                      raw        ,
 autoflush                          in                  boolean default
);

The init parameter utl_file_dir and directory

Up to 8i, Oracle refused to access a file that is not pointed to in the utl_file_dir parameter in the init<sid>.ora file.
In Oracle 9i, in order to access a file, either the utl_file_dir parameter must be set, or one has to create a directory.
The disadvantage of the init param is that if it is used, it is valid for all users in the database. There is no way (other than totally disable utl_file for someone) to selectively restrict directories to someone. Using directories, it is possible to grant directories to some users according to their needs. Thus, the security risk is smaller.

An example

The following two procedures show how to use utl_file to write to and read from a file using PL/SQL. It doesn't do very much let alone something useful, but it can be extended.
In order to use it, make sure the utl_file_dir paramter is set: 
select value from v$parameter where name = 'utl_file_dir';
The value returned is actually the path that you must use in the arguments to utl_file_test_read and utl_file_test_write.
utl_file_test_write writes two lines into the file specified with the parameters path and filename. 
create or replace procedure utl_file_test_write (
  path       in varchar2,
  filename   in varchar2,
  firstline  in varchar2, 
  secondline in varchar2)
is
    output_file  utl_file.file_type;
begin
    output_file := utl_file.fopen (path,filename, 'W');

    utl_file.put_line (output_file, firstline);
    utl_file.put_line (output_file, secondline);
    utl_file.fclose(output_file);

  --exception
  --  when others then null;
end;
/
utl_file_test_read reads two lines from the file specified with the parameters path and filename and prints them using dbms_output. 
create or replace procedure utl_file_test_read (
  path       in varchar2,
  filename   in varchar2)
is
  input_file   utl_file.file_type;
  input_buffer varchar2(4000);
begin
  input_file := utl_file.fopen (path,filename, 'R');

  utl_file.get_line (input_file, input_buffer);
  dbms_output.put_line(input_buffer);
  utl_file.get_line (input_file, input_buffer);
  dbms_output.put_line(input_buffer);
  utl_file.fclose(input_file);

  --exception
  -- when others then null;
end;
/
Creating and writing to a file: 
begin
  utl_file_test_write (
    '/tmp',
    'utl_file_test',
    'first line',
    'second line'
  );
end;
/
Now, reading from the file: 
set serveroutput on size 1000000

begin
  utl_file_test_read('/tmp','utl_file_test');
end;
/
Also check in your utl_file_dir that the file was created.

Links

On reading trace files with PL/SQL.
See also create directory for another example.