IDC资讯中心
pl/sql 3.3以上的版本中，utl_file包允许用户通过pl/sql读写操作系统文件。如下：
declare
file_handle utl_file.file_type;
begin
file_handle := utl_file.fopen(tmp, 文件名, w,[1-32767]);
–四个参数：目录，文件名，打开方式，最大行数(默认为2000)
utl_file.putf(file_handle, 写入的信息\n);
utl_file.fclose(file_handle);
exception
when utl_file.invalid_path then
raise_application_error(-20000, error: invalid path for file or path not in init.ora.);
end;
putf()过程用来以指定格式把文本写入一个文件
put_line()过程把一个指定的字符串写入文件并在文件中开始新的一行
9i中函数说明：
utl_file.fopen (
location in varchar2,
filename in varchar2,
open_mode in varchar2,
max_linesize in binary_integer)
return file_type;
parameters description
location (in) the directory location of the source file, a directory_name from the all_directories view (case sensitive)
filename file name, including extension (file type), without directory path. in unix,the filename cannot end with /.
一个使用文件的测试
1、用sys用户登录到数据库(ora9i):conn sys/orcl@eai01 as sysdba
2、在sqlplus中运行create or replace directory tmp as d:\testtmp;
在sqlplus中运行grant read on directory tmp to public
3、在scott用户下建立存储过程如：preadfiletest
create or replace procedure preadfiletest
(fpath in string,fname in string,max_num in number)
is
file_handle utl_file.file_type;
text_buffer string(1000);
line_num number;
begin
dbms_output.put_line(input path=||fpath);
dbms_output.put_line(input filename=||fname);
line_num :=0;
begin
file_handle := utl_file.fopen(fpath,fname,r,max_num);
loop
line_num:= line_num + 1;
utl_file.get_line(file_handle,text_buffer);
dbms_output.put_line(line||line_num|| : ||text_buffer);
end loop;
exception
when no_data_found then
return;
when utl_file.invalid_path then
dbms_output.put_line(invalid path);
when utl_file.invalid_mode then
dbms_output.put_line(invalid mode);
when utl_file.invalid_filehandle then
dbms_output.put_line(invalid filehandle);
when utl_file.invalid_operation then
dbms_output.put_line(invalid operation);
when utl_file.read_error then
dbms_output.put_line(read error);
when utl_file.write_error then
dbms_output.put_line(write error);
when utl_file.internal_error then
dbms_output.put_line(internal error);
when others then
dbms_output.put_line(sqlerrm);
end;
exception
when others then
dbms_output.put_line(other error=||sqlerrm);
end preadfiletest;
4、在sqlplus中运行set serveroutput on
5、exec preadfiletest(tmp,a.txt,r)
6、正常输出
小结：在9i中可以不在init.ora中增加utl_file_dir目录，而是在sys用户下创建一个directory目录（实际上是在sys用户下的dir$表中增加一个对应的os_path），然后将对该directory对象的读/写操作的权限grant给public。
