使用Oracle UTL_FILE包操作文件
本文档仅作为参考使用，如需要关于UTL_FILE包的官方详细描述，请点击这里，更多例子，请参考这里。
UTL_FILE封装了PL/SQL程序中对文件的操作，通常被用于PL/SQL程序的日志记录。
在使用之前，先必须确定好UTL_FILE包的参数设置是否正确，通常只需要设置‘utl_file_dir’参数就即可，‘utl_file_dir’定义了所操作文件的根目录。
检查参数
utl_file_dir的值可以有多个，设置多个目录时使用逗号分隔符，在使用fopen函数的时候必须指定其中任何一个目录即可，如果utl_file_dir只有一个可用目录，则不需要指定（见后面的例子）
设置参数
使用如下的命令重置utl_file_dir的值，指定你所需要的目录即可。
ALTER SYSTEM SET utl_file_dir='/tmp/','/oradata','/home/'
使用自定义目录
如果不使用utl_file_dir参数设置目录，也可是使用自定义目录来定义文件读写根目录，但必须对目录拥有相应的权限，如果没有则需要sysadm授权
CREATE DIRECTORY oraload AS 'c:\oraload\';
GRANT READ,WRITE ON DIRECTORY oraload TO UWCLASS;
常用方法
utl_file.fopen(file_location IN VARCHAR2, file_name IN VARCHAR2, open_mode IN VARCHAR2, max_linesize IN BINARY_INTEGER DEFAULT NULL) RETURN file_type;
fopen方法打开一个文件，函数返回类型为utl_file.file_type的文件句柄。
参数：
file_location：文件路径可以为utl_file_dir中的任何一个路径，或者使用自定义路径，在使用自定义路径或者utl_file_dir（value中只有一个路径）时，参数名必须大写。utl_file.fopen('ORALOAD', ...)
file_name：文件名
open_mode：文件打开模式，有一下几种
A：Append 文本
AB：Append字节
R：文本只读
RB：字节只读
W：文本写入
WB：字节写入
常用的为A、R、W
max_linesize：每行字符数的最大值，最小为1，最大为32767，默认使用1024
utl_file.get_line(file IN FILE_TYPE,buffer OUT VARCHAR2,len IN BINARY_INTEGER DEFAULT NULL);
getline方法从指定文件句柄中读取一行数据，可以指定每次读取内容的大小。
参数：
file：使用fopen打开的文件句柄
buffer：接受读取内容的缓存
len：读取内容的字节大小，默认将会使用最大值
UTL_FILE.PUT_LINE (file IN FILE_TYPE, buffer IN VARCHAR2, autoflush IN BOOLEAN DEFAULT FALSE);
向指定文件中写入一行内容。
参数：
file：要写入内容的目标文件句柄
buffer：需要写入的内容
autoflash：是否在write之后将内容从缓冲区写入磁盘
UTL_FILE.IS_OPEN (file IN FILE_TYPE) RETURN BOOLEAN;
用来判断指定文件句柄是否为open状态，返回类型为布尔值
UTL_FILE.FCLOSE (file IN OUT FILE_TYPE);
用来关闭一个已经打开的文件句柄，在使用完文件后，必须关闭文件句柄。
例子
下面这段代码讲会从指定文件中读取数据库表名，然后将表的部分信息以html格式输出到文件中。
1 /* 2 Before running this script, please use command 'show parameter utl_file' to check 'utl_file_dir' parameter, 3 and then set the v_log_path with one of the value of the 'utl_file_dir' values. Please inform your sysdba to 4 set the 'utl_file_dir' parameter. 5 */ 6 SET serveroutput ON; 7 DECLARE 8 v_log_path VARCHAR2(1000) DEFAULT '/usr/tmp'; 9 v_log_file_appender VARCHAR2(1000) DEFAULT ''; 10 v_health_check_tables_file utl_file.file_type; 11 v_health_check_file_name varchar2(100) default 'health_check_tables.txt'; 12 v_perf_log utl_file.file_type; 13 v_flag_column_info boolean default false; 14 15 v_table_name VARCHAR2(100) DEFAULT 'MTL_SYSTEM_ITEMS_B'; 16 v_data_vol_count NUMBER DEFAULT 0; 17 v_data_vol_query_str VARCHAR2(1000); 18 v_table_stats_record all_tables%rowtype; 19 v_table_stats_record_num NUMBER DEFAULT 0; 20 v_column_stats_record all_tab_columns%rowtype; 21 v_index_stats_record all_indexes%rowtype; 22 23 CURSOR v_cursor_column_rec(p_table_name VARCHAR2) IS 24 SELECT * FROM all_tab_columns WHERE table_name = p_table_name ORDER BY LAST_ANALYZED ASC; 25 26 CURSOR v_cursor_index_rec(p_table_name VARCHAR2) IS 27 select * from all_indexes where table_name = p_table_name order by last_analyzed asc; 28 BEGIN 29 v_log_file_appender := to_char(sysdate, 'yyyy-mm-dd') || '_' || to_char(sysdate, 'SSSSS'); 30 --open log file 31 v_perf_log := utl_file.fopen(v_log_path, 'pim_healthcheck_'|| v_log_file_appender ||'.html', 'A'); 32 IF (utl_file.is_open(v_perf_log)) THEN 33 v_health_check_tables_file := utl_file.fopen(v_log_path, v_health_check_file_name, 'R'); 34 end if; 35 36 loop 37 BEGIN 38 utl_file.get_line(v_health_check_tables_file, v_table_name); 39 v_table_name := REPLACE(v_table_name, chr(10), ''); 40 v_table_name := REPLACE(v_table_name, chr(13), ''); 41 v_table_name := upper(rtrim(ltrim(v_table_name))); 42 IF (v_table_name IS NOT NULL) THEN 43 utl_file.put_line(v_perf_log, '<h2>Table ' || v_table_name || '</h2>'); 44 -- data volumn check 45 v_data_vol_query_str := 'select count(*) from ' || v_table_name; 46 EXECUTE IMMEDIATE v_data_vol_query_str INTO v_data_vol_count; 47 utl_file.put_line(v_perf_log, 'The number of rows in table ' || v_table_name || ' is ' || to_char(v_data_vol_count)); 48 -- table static information 49 SELECT count(*) INTO v_table_stats_record_num FROM all_tables WHERE table_name = v_table_name; 50 utl_file.put_line(v_perf_log, '<table border=1>'); 51 utl_file.put_line(v_perf_log, '<tr><th>Last Analyze Date</th><th>NUM_ROWS</th><th>BLOCKS</th><th>AVG_ROW_LEN</th></tr>'); 52 IF (v_table_stats_record_num > 0) THEN 53 SELECT * INTO v_table_stats_record FROM all_tables WHERE table_name = v_table_name AND ROWNUM = 1; 54 utl_file.put_line(v_perf_log, '<tr>'); 55 utl_file.put_line(v_perf_log, '<td>' || to_char(v_table_stats_record.last_analyzed, 'yyyy/mm/dd HH24:MI:SS') || '</td>'); 56 IF (v_table_stats_record.num_rows < v_data_vol_count) THEN 57 utl_file.put_line(v_perf_log, '<td>' || to_char(v_table_stats_record.num_rows) || '</td>'); -- can mark alert info 58 ELSE 59 utl_file.put_line(v_perf_log, '<td>' || to_char(v_table_stats_record.num_rows) || '</td>'); 60 END IF; 61 utl_file.put_line(v_perf_log, '<td>'||to_char(v_table_stats_record.BLOCKS)||'</td>'); 62 utl_file.put_line(v_perf_log, '<td>'||to_char(v_table_stats_record.avg_row_len)||'</td>'); 63 utl_file.put_line(v_perf_log, '</tr>'); 64 END IF; 65 utl_file.put_line(v_perf_log, '</table>'); 66 -- table definition area 67 --1. column definition 68 IF (v_flag_column_info) THEN 69 utl_file.put_line(v_perf_log, '<h3>Column information</h3>'); 70 utl_file.put_line(v_perf_log, '<table border=1>'); 71 utl_file.put_line(v_perf_log, '<tr><th>COLUMN_NAME</th><th>DATA_TYPE</th><th>DATA_LENGTH</th><th>LAST_ANALYZED</th></tr>'); 72 OPEN v_cursor_column_rec(v_table_name); 73 loop 74 fetch v_cursor_column_rec INTO v_column_stats_record; 75 utl_file.put_line(v_perf_log, '<tr>'); 76 utl_file.put_line(v_perf_log, '<td>'||to_char(v_column_stats_record.COLUMN_NAME)||'</td>'); 77 utl_file.put_line(v_perf_log, '<td>'||to_char(v_column_stats_record.DATA_TYPE)||'</td>'); 78 utl_file.put_line(v_perf_log, '<td>'||to_char(v_column_stats_record.DATA_LENGTH)||'</td>'); 79 utl_file.put_line(v_perf_log, '<td>'||to_char(v_column_stats_record.LAST_ANALYZED)||'</td>'); 80 utl_file.put_line(v_perf_log, '</tr>'); 81 exit when v_cursor_column_rec%NOTFOUND; 82 END loop; 83 CLOSE v_cursor_column_rec; 84 end if; 85 -- 2. index information 86 utl_file.put_line(v_perf_log, '<h3>Index information</h3>'); 87 utl_file.put_line(v_perf_log, '<table border=1>'); 88 utl_file.put_line(v_perf_log, '<tr><th>INDEX_NAME</th><th>INDEX_TYPE</th><th>LAST_ANALYZED</th><th>NUM_ROWS</th></tr>'); 89 OPEN v_cursor_index_rec(v_table_name); 90 loop 91 fetch v_cursor_index_rec INTO v_index_stats_record; 92 utl_file.put_line(v_perf_log, '<tr>'); 93 utl_file.put_line(v_perf_log, '<td>'||to_char(v_index_stats_record.INDEX_NAME)||'</td>'); 94 utl_file.put_line(v_perf_log, '<td>'||to_char(v_index_stats_record.INDEX_TYPE)||'</td>'); 95 utl_file.put_line(v_perf_log, '<td>'||to_char(v_index_stats_record.LAST_ANALYZED)||'</td>'); 96 utl_file.put_line(v_perf_log, '<td>'||to_char(v_index_stats_record.NUM_ROWS)||'</td>'); 97 utl_file.put_line(v_perf_log, '</tr>'); 98 exit when v_cursor_index_rec%NOTFOUND; 99 END loop; 100 utl_file.put_line(v_perf_log, '</table>'); 101 CLOSE v_cursor_index_rec; 102 end if; 103 exception 104 WHEN NO_DATA_FOUND THEN 105 exit; 106 END; 107 end loop; 108 109 utl_file.fclose(v_perf_log); 110 utl_file.fclose(v_health_check_tables_file); 111 EXCEPTION 112 WHEN UTL_FILE.INVALID_PATH THEN 113 -- error during opening log file 114 dbms_output.put_line('Error: please check your "utl_file_dir" setting first.'); 115 WHEN OTHERS THEN 116 IF (utl_file.is_open(v_perf_log)) THEN 117 -- close file if there is any exception; 118 utl_file.fclose(v_perf_log); 119 END IF; 120 IF (utl_file.is_open(v_health_check_tables_file)) THEN 121 utl_file.fclose(v_health_check_tables_file); 122 end if; 123 END;
文件health_check_tables.txt的格式为：
Table_Name_A
Table_Name_B
