E.invalid_path
THEN
raise_application_error (-20052, 'Invalid File Location');
WHEN UTL_FILE.invalid_filehandle
THEN
raise_application_error (-20053, 'Invalid Filehandle');
WHEN UTL_FILE.invalid_operation
THEN
raise_application_error (-20054, 'Invalid Operation');
WHEN UTL_FILE.read_error
THEN
raise_application_error (-20055, 'Read Error');
WHEN UTL_FILE.internal_error
THEN
raise_application_error (-20057, 'Internal Error');
WHEN UTL_FILE.charsetmismatch
THEN
raise_application_error (-20058, 'Opened With FOPEN_NCHAR
But Later I/O Inconsistent');
WHEN UTL_FILE.file_open
THEN
raise_application_error (-20059, 'File Already Opened');
WHEN UTL_FILE.invalid_maxlinesize
THEN
raise_application_error (-20060, 'Line Size Exceeds 32K');
WHEN UTL_FILE.invalid_filename
THEN
raise_application_error (-20061, 'Invalid File Name');
WHEN UTL_FILE.access_denied
THEN
raise_application_error (-20062, 'File Access Denied By');
WHEN UTL_FILE.invalid_offset
THEN
raise_application_error (-20063, 'FSEEK Param Less Than 0');
WHEN OTHERS
THEN
raise_application_error (-20099, 'Unknown UTL_FILE Error');
END read_demo;
/
--下面是读写模式过程的代码，这个过程实现了从一个数据文件读出并写入到另外一个数据文件
CREATE OR REPLACE PROCEDURE rw_demo
IS
infile UTL_FILE.file_type;
outfile UTL_FILE.file_type;
vnewline VARCHAR2 (4000);
i PLS_INTEGER;
j PLS_INTEGER := 0;
seekflag BOOLEAN := TRUE;
BEGIN
-- open a file to read
infile := UTL_FILE.fopen ('DB_UTL_DIR', 'x.txt', 'r'); -->打开源文件用于读取数据
-- open a file to write
outfile := UTL_FILE.fopen ('DB_UTL_DIR', 'out.txt', 'w'); -->创建目标文件用于存放数据
-- if the file to read was successfully opened
IF UTL_FILE.is_open (infile)
THEN
-- loop through each line in the file
LOOP
BEGIN
UTL_FILE.get_line (infile, vnewline); -->从源文件读取行
i := UTL_FILE.fgetpos (infile); -->将行的位置赋值并输出
DBMS_OUTPUT.put_line (TO_CHAR (i));
UTL_FILE.put_line (outfile, vnewline, FALSE); -->将得到的数据行写出到文件句柄缓冲
UTL_FILE.fflush (outfile); -->将数据行从缓冲区写入到文件
IF seekflag = TRUE
THEN
UTL_FILE.fseek (infile, NULL, -30); -->用于调整文件指针，即偏移量
seekflag := FALSE;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
EXIT;
END;
END LOOP;
COMMIT;
END IF;
UTL_FILE.fclose (infile); -->关闭源文件
UTL_FILE.fclose (outfile); -->关闭目标文件
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20099, 'Unknown UTL_FILE Error');
END rw_demo;
/
注意在使用UTL_FILE包用到DIRECTORY数据库对象时，名字一定要大写，否则会遭遇“ORA-29280: invalid directory path”错误
主要参考:
http://psoug.org/reference/utl_file.html http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/u_file.htm#BABGGEDF
更多参考
使用 DBMS_PROFILER 定位 PL/SQL 瓶颈代码
使用PL/SQL Developer剖析PL/SQL代码
对比 PL/SQL profiler 剖析结果
PL/SQL Profiler 剖析报告生成html
DML Error Logging 特性
PL/SQL --> 游标
PL/SQL --> 隐式游标(SQL%FOUND)
批量SQL之 FORALL 语句
批量SQL之 BULK COLLECT 子句
PL/SQL 集合的初始化与赋值
PL/SQL 联合数组与嵌套表
SQL tuning 步骤
高效SQL语句必杀技
父游标、子游标及共享游标
绑定变量及其优缺点
dbms_xplan之display_cursor函数的使用
dbms_xplan之display函数的使用
执行计划中各字段各模块描述
使用 EXPLAIN PLAN 获取SQL语句执行计划