3.1设置utl_file_dir参数
SQL> alter system set utl_file_dir='/u01/app/oracle' scope=spfile;
System altered.
SQL> startupforce;
SQL> show parameter utl_file
NAME TYPE VALUE
-------------------------------- ----------- ------------------------------
utl_file_dir string /u01/app/oracle
设置多个路径:
SQL> alter system set utl_file_dir='/u01/app/oracle', '/oradata'scope=spfile;
System altered.
SQL> startup force
NAME TYPE VALUE
-------------------------------- -----------------------------------------
utl_file_dir string /u01/app/oracle, oradata
3.2 utl_file的IO操作
SQL> declare
fn utl_file.file_type;
begin
fn := utl_file.fopen('/u01/app/oracle', 'utl_test.txt', 'W');
utl_file.fclose(fn);
end;
/
PL/SQL procedure successfully completed.
不是utl_file_dir所指定的路径时,使用fopen方法时就会报错:
SQL> declare
fn utl_file.file_type;
begin
fn := utl_file.fopen('/u01/app/oracle/admin', 'utl_test.txt', 'W');
utl_file.fclose(fn);
end;
/
declare
*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 33
ORA-06512: at "SYS.UTL_FILE", line 436
ORA-06512: at line 4
为了避免上面的错误,可以使用路径对象。
SQL> create directory dir_test as '/oradata';
Directory created.
SQL> declare
fn utl_file.file_type;
begin
fn := utl_file.fopen('DIR_TEST', 'test.txt', 'W');
utl_file.fclose(fn);
end;
/
3.2UTL_FILE包详解
Oracle虽然有SQL*Loader可以将文本的内容读到数据库里,但是不能将数据库内容输出到文本。所以基本上是要用到utl_file包来操作。先看了一下Oracle官方文档中的介绍,utl_file推荐直接使用自己创建的DIRECTORY来操作文件,而不要继续使用UTL_FILE_DIR包来指定。
utl_file包的工作机制是这样的:首先要使用FOPEN函数,将文件的路径、文件名、以及打开模式的参数传入,然后Oracle会到ALL_DIRECTORIES视图中查看路径是否已经创建。如果路径和文件名均合法,则该文件被打开到一个file_type中,然后可以进行各种操作,最后使用FCLOSE函数将其关闭。
写入文件
(1)通过UTL_FILE.FOPEN方法找到对应路径,创建文件,并且给出写入规则。(2)通过UTL_FILE.PUT_LINE方法向文件中写入内容(UTL_FILE.PUT_LINE写入VARCHAR2类型数据,UTL_FILE.PUT_RAW方法是写入RAW类型的数据,一般来说RAW容量更大,用的更加广泛),这里由于ORACLE有长度限制,一般采用循环方式分批写入。(3)写入完成后,通过UTL_FILE.FCLOSE方法关闭文件,结束写出。
读入文件
使用utl_file读入文件,通过读取指定文件,将读取的内容写入库中,总体过程如下:(1)通过UTL_FILE.FOPEN方法找到对应路径,读取文件(文件一定要存在),并且给出读入规则。(2)通过UTL_FILE.GET_LINE方法循环向变量中写入内容(UTL_FILE.PUT_LINE写入VARCHAR2类型数据,UTL_FILE.PUT_RAW方法是写入RAW类型的数据,一般来说RAW容量更大,用的更加广泛),这里由于逐行读取,所以要循环操作(报NO_DATA_FOUND异常,即没有数据后跳出循环)。(3)将内容变量INSERT到指定库表内(4)写入完成后,通过UTL_FILE.FCLOSE方法关闭文件,结束读入。
测试写入:
DECLARE
filehandleutl_file.file_type; --句柄
begin
filehandle:= utl_file.fopen('/home/oracle/utl','utl_test.txt','w'); --打开文件
utl_file.put_line(filehandle,'HelloOracle!');--写入一行记录
utl_file.put_line(filehandle,'HelloWorld!');
utl_file.put_line(filehandle,'你好,胖子!');
utl_file.fclose(filehandle);--关闭句柄
end;
/
测试读取:
--测试读取
setserveroutput on;
DECLARE
filehandleutl_file.file_type;
filebuffervarchar2(500);
begin
filehandle:= utl_file.fopen('/home/oracle/utl','utl_test.txt','R');
IFutl_file.is_open(filehandle) THEN
dbms_output.put_line('fileis open!');
ENDIF;
loop
begin
utl_file.get_line(filehandle,filebuffer);
dbms_output.put_line(filebuffer);
EXCEPTION
WHENno_data_found THEN
exit;
WHENOTHERS THEN
dbms_output.put_line('EXCEPTION1:'||SUBSTR(SQLERRM,1, 100)) ;
end;
endloop;
utl_file.fclose(filehandle);
IFutl_file.is_open(filehandle) THEN
dbms_output.put_line('fileis open!');
else
dbms_output.put_line('fileis close!');
ENDIF;
utl_file.fcopy('/home/oracle/utl','utl_test.txt', '/home/oracle/utl', 'utl_test.dat');--复制
utl_file.fcopy('/home/oracle/utl','utl_test.txt', '/home/oracle/utl', 'utl_test2.dat');
utl_file.fcopy('/home/oracle/utl','utl_test.txt', '/home/oracle/utl', 'utl_test.xls');
utl_file.frename('/home/oracle/utl','utl_test.xls','/home/oracle/utl','frenamehello.xls',TRUE);--重命名
utl_file.fremove('/home/oracle/utl','utl_test2.dat');--删除文件
EXCEPTION
WHENOTHERS THEN
dbms_output.put_line('EXCEPTION2:'||SUBSTR(SQLERRM,1, 100)) ;
end;
/
fileis open!
HelloOracle!
HelloWorld!
你好,胖子!
fileis close!