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.
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.
No comments:
Post a Comment