Oracle UTLFILE的使用.docx
《Oracle UTLFILE的使用.docx》由会员分享,可在线阅读,更多相关《Oracle UTLFILE的使用.docx(13页珍藏版)》请在冰豆网上搜索。
![Oracle UTLFILE的使用.docx](https://file1.bdocx.com/fileroot1/2023-6/10/890d1461-84f8-49bc-a80b-ea3ec60e48d6/890d1461-84f8-49bc-a80b-ea3ec60e48d61.gif)
OracleUTLFILE的使用
OracleUTL_FILE的使用
使用dbms_output输出有一个缺点是要到整个过程执行完毕才会把结果输出到屏幕,这样就无法在一个长时间运行的过程中通过dbms_output输出来随时监控执行状况。
为此可以使用utl_file包来输出文件。
utl_file.fopen打开文件
utl_file.put_line输出文件
utl_file.get_line读取文件
utl_file.fclose关闭文件
utl_file.fflush强制输出缓冲
utl_file.fopen的第一个参数指定文件所在目录,这个目录必须包含在utl_file_dir参数指定的目录列表中,或者指定一个directory对象。
否则fopen()会报错
。
-----*********指定路径的两种方式**************
1)utl_file_dir
设置这个参数必须重起数据库,可以指定多个目录(用逗号隔开)。
如果指定为*,表示任意目录。
altersystemsetutl_file_dir='/u01/oracle','/u02/oracle'scope=spfile;
2)directory
创建一个directory对象并赋予所有用户读写权利
createorreplacedirectorylogfile_targetas'/u01/oracle';
grantread,writeondirectorylogfile_targettopublic;
察看已有的directory对象
select*fromdba_directories;
********************************************************************************************
-----批注(关于directory)
Oracle创建directory
一般创建directory都是为了用数据泵导入/导出数据用,其实directory还有很多别的用处,本文不做阐述
1、新建directory的语法
CREATE[ORREPLACE]DIRECTORYdirectoryAS'pathname';
例如:
createorreplacedirectorydump_diras'D:
\dump\dir'
这样把目录d:
\dump\dir设置成dump_dir代表的directory
2、查询有哪些directory
select*fromdba_directories
3、赋权
grantread,writeondirectorydump_dirtouser01
4、删除
dropdirectorydump_dir
-----创建目录,在可以在oracle中使用目录
比如通过pl/sql向目录中写文件,外部表,数据泵,bfile类型都会使用到。
oralce内部用来识别OS文件系统路径用的。
比如在plsql中进行log输出,制定具体文件路径时要使用到。
*****************************************************************************************************
3)使用utl_file包输出文件
DECLARE
l_fileutl_file.file_type;----------定义一个文件句柄
BEGIN
l_file:
=utl_file.fopen('LOGFILE_TARGET','sql.log','W');
forrowin(select*fromuser_tables)
loop
utl_file.put_line(l_file,dbms_metadata.get_ddl('TABLE',row.table_name));
endloop;
utl_file.fclose(l_file);
END;
*这里也可以写成utl_file.fopen('/u01/oracle','sql.log','W');
*fopen()的第三个参数OPEN_MODE,可以指定为
r--readtext
w--writetext
a--appendtext
rb--readbytemode
wb--writebytemode
ab--appendbytemode
如果指定'a'或者'ab'但是文件不存在会先创建。
Oracle的utl_file来读取txt文件
Oracle的utl_file来读取txt文件,自己参考了两个实例,自己也动手稍微写了一下,来和大家分享一下,不是太完整,希望大家多多指点。
工具/原料
∙本人是在C盘下面建了一个test.txt的文件,内容如下:
hellooracle!
你好,胖子!
∙还有一个test表
CREATETABLETEST(aVARCHAR(30),bVARCHAR(30));
方法/步骤
1.1
创建一个directory:
createorreplacedirectoryFILENAMEas'C:
\TEMP';
2.2
往文件里面写内容:
--写txt文件
declarefilehandleutl_file.file_type;--句柄
begin
filehandle:
=utl_file.fopen('路径名','test1.txt','w');-----------(路径名,文件名,读写方式)
utl_file.put_line(filehandle,'hellooracle!
');
utl_file.put_line(filehandle,'你好,胖子!
');
utl_file.fclose(filehandle);--关闭句柄
end;
3.3
读取txt文件中的内容,并写入到表中:
--读取txt文件
/*setserveroutputON*/
DECLARE
filehandleutl_file.file_type;
filebuffervarchar(200);
BEGIN
filehandle:
=utl_file.fopen('FILENAME','test1.txt','r');
loop
begin
utl_file.get_line(filehandle,filebuffer);
INSERTINTOTEST(a)VALUES(filebuffer);
EXCEPTION
WHENno_data_foundTHEN
exit;
End;
ENDLOOP;
utl_file.fclose(filehandle);
COMMIT;
END;
4.4
演示结果:
如图
select*fromtest;
5.5
注意:
filehandle:
=utl_file.fopen('FILENAME','test1.txt','w');
此处的FILENAME必须为大写,否则会提示:
ERROR位于第1行:
ORA-29280:
目录路径无效
ORA-06512:
在"SYS.UTL_FILE",line18
ORA-06512:
在"SYS.UTL_FILE",line424
ORA-06512:
在line4
END
注意事项
∙filehandle:
=utl_file.fopen('FILENAME','test1.txt','w');
此处的FILENAME必须为大写,否则会提示:
ERROR位于第1行:
ORA-29280:
目录路径无效
ORA-06512:
在"SYS.UTL_FILE",line18
ORA-06512:
在"SYS.UTL_FILE",line424
ORA-06512:
在line4
∙要是你试着不好用的话,就自己新开一个sqlwidnow对话框,运行程序即可
utl_file包的使用(重要--)
包中主要的函数
FOPEN
IS_OPEN
GET_LINE
PUT
NEW_LINE
PUT_LINE
PUTF
FFLUSH
FCLOSE
FCLOSE_ALL
使用utl_file包之前应先建立目录
createorreplacedirectoryBFILE_DIRas
'f:
/home/oracle/bfiletest';
给用户读写该目录的权限
grantread,writeondirectoryBFILE_DIR tolunar;
GRANTEXECUTEONutl_fileTOwangyh
UTL_FILE.FOPEN
用法
FOPEN会打开指定文件并返回一个文件句柄用于操作文件。
FUNCTIONUTL_FILE.FOPEN(
location INVARCHAR2,
filename INVARCHAR2,
open_mode INVARCHAR2,
max_linesizeINBINARY_INTEGER)
RETURNfile_type;
参数
location
文件地址
filename
文件名
openmode
打开文件的模式(参见下面说明)
max_linesize
文件每行最大的字符数,包括换行符。
最小为1,最大为32767
3种文件打开模式:
R只读模式。
一般配合UTL_FILE的GET_LINE来读文件。
W写(替换)模式。
文件的所有行会被删除。
PUT,PUT_LINE,NEW_LINE,PUTF和FFLUSH都可使用
A写(附加)模式。
原文件的所有行会被保留。
在最末尾行附加新行。
PUT,PUT_LINE,NEW_LINE,PUTF和FFLUSH都可使用
打开文件时注意以下几点:
文件路径和文件名合起来必须表示操作系统中一个合法的文件。
文件路径必须存在并可访问;FOPEN并不会新建一个文件夹。
如果你想打开文件进行读操作,文件必须存在;如果你想打开文件进行写操作,文件不存在时,会新建一个文件。
如果你想打开文件进行附加操作,文件必须存在。
A模式不同于W模式。
文件不存在时,会抛出INVALID_OPERATION异常。
FOPEN会抛出以下异常
UTL_FILE.INVALID_MODE
UTL_FILE.INVALID_OPERATION
UTL_FILE.INVALID_PATH
UTL_FILE.INVALID_MAXLINESIZE
UTL_FILE.IS_OPEN用法
如果文件句柄指定的文件已打开,返回TRUE,否则FALSE
FUNCTIONUTL_FILE.IS_OPEN(fileINUTL_FILE.FILE_TYPE)RETURNBOOLEAN;
UTL_FILE只提供一个方法去读取数据:
GET_LINE
UTL_FILE.GET_LINE用法
读取指定文件的一行到提供的缓存。
PROCEDUREUTL_FILE.GET_LINE
(fileINUTL_FILE.FILE_TYPE,
bufferOUTVARCHAR2);
file
由FOPEN返回的文件句柄
buffer
读取的一行数据的存放缓存
buffer必须足够大。
否则,会抛出VALUE_ERROR异常。
行终止符不会被传进buffer。
异常
NO_DATA_FOUND
VALUE_ERROR
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.INVALID_OPERATION
UTL_FILE.READ_ERROR
UTL_FILE.PUT用法
在当前行输出数据
PROCEDUREUTL_FILE.PUT
(fileINUTL_FILE.FILE_TYPE,
bufferOUTVARCHAR2);
file
由FOPEN返回的文件句柄
buffer
包含要写入文件的数据缓存;Oracle8.0.3及以上最大允许32kB,早期版本只有1023B
UTL_FILE.PUT输出数据时不会附加行终止符。
UTL_FILE.PUT会产生以下异常
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.INVALID_OPERATION
UTL_FILE.WRITE_ERROR
UTL_FILE.NEW_LINE---------
在当前位置输出新行或行终止符,(新建一个空行)必须使用NEW_LINE来结束当前行,或者使用PUT_LINE输出带有行终止符的完整行数据。
PROCEDUREUTL_FILE.NEW_LINE
(fileINUTL_FILE.FILE_TYPE,
linesINNATURAL:
=1);
file
由FOPEN返回的文件句柄
lines
要插入的行数
如果不指定lines参数,NEW_LINE会使用默认值1,在当前行尾换行。
如果要插入一个空白行,可以使用以下语句:
UTL_FILE.NEW_LINE(my_file,2);
如果lines参数为0或负数,什么都不会写入文件。
NEW_LINE会产生以下异常
VALUE_ERROR
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.INVALID_OPERATION
UTL_FILE.WRITE_ERROR
例子
如果要在UTL_FILE.PUT后立刻换行,可以如下例所示:
PROCEDUREadd_line(file_inINUTL_FILE.FILE_TYPE,line_inINVARCHAR2)
IS
BEGIN
UTL_FILE.PUT(file_in,line_in);
UTL_FILE.NEW_LINE(file_in);
END;
UTL_FILE.PUT_LINE
输出一个字符串以及一个与系统有关的行终止符
PROCEDUREUTL_FILE.PUT_LINE
(fileINUTL_FILE.FILE_TYPE,
bufferINVARCHAR2);
file
由FOPEN返回的文件句柄
buffer
包含要写入文件的数据缓存;Oracle8.0.3及以上最大允许32kB,早期版本只有1023B
在调用UTL_FILE.PUT_LINE前,必须先打开文件。
UTL_FILE.PUT_LINE会产生以下异常
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.INVALID_OPERATION
UTL_FILE.WRITE_ERROR
例子
这里利用UTL_FILE.PUT_LINE从表emp读取数据到文件:
declare
fileIDUTL_FILE.FILE_TYPE;
BEGIN
fileID:
=UTL_FILE.FOPEN('BFILE_DIR','emp.TXT','W');
/*Quickanddirtyconstructionhere!
*/
FORemprecIN(SELECT*FROMemp)
LOOP
UTL_FILE.PUT_LINE
(FILEID,TO_CHAR(emprec.empno)||','||
emprec.ename||','||
TO_CHAR(emprec.deptno));
ENDLOOP;
UTL_FILE.FCLOSE(fileID);
END;
/
GRANTREADONDIRECTORYBFILE_DIRTOSCOTT;
GRANTwriteONDIRECTORYBFILE_DIRTOSCOTT;PUT_LINE相当于PUT后加上NEW_LINE;也相当于PUTF的格式串"%s\n"。
UTL_FILE.PUTF
以一个模版样式输出至多5个字符串,类似C中的printf
PROCEDUREUTL_FILE.PUTF
(fileINFILE_TYPE
,formatINVARCHAR2
,arg1INVARCHAR2DEFAULTNULL
,arg2INVARCHAR2DEFAULTNULL
,arg3INVARCHAR2DEFAULTNULL
,arg4INVARCHAR2DEFAULTNULL
,arg5INVARCHAR2DEFAULTNULL);
file
由FOPEN返回的文件句柄
format
决定格式的格式串
argN
可选的5个参数,最多5个
格式串可使用以下样式
%s
在格式串中可以使用最多5个%s,与后面的5个参数一一对应
\n
换行符。
在格式串中没有个数限制
%s会被后面的参数依次填充,如果没有足够的参数,%s会被忽视,不被写入文件
UTL_FILE.PUTF会产生以下异常
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.INVALID_OPERATION
UTL_FILE.WRITE_ERROR
declare
fileIDUTL_FILE.FILE_TYPE;
BEGIN
fileID:
=UTL_FILE.FOPEN('BFILE_DIR','emp.TXT','W');
/*Quickanddirtyconstructionhere!
*/
FORemprecIN(SELECT*FROMemp)
LOOP
UTL_FILE.PUTf
(FILEID,TO_CHAR(emprec.empno)||','||
emprec.ename||','||
TO_CHAR(emprec.deptno),',');
utl_file.new_line(fileid);
ENDLOOP;
UTL_FILE.FCLOSE(fileID);
END;
/
UTL_FILE.FFLUSH
确保所有数据写入文件。
PROCEDUREUTL_FILE.FFLUSH(fileINUTL_FILE.FILE_TYPE);
file
由FOPEN返回的文件句柄
操作系统可能会缓存数据来提高性能。
因此可能调用put后,打开文件却看不到写入的数据。
在关闭文件前要读取数据的话可以使用UTL_FILE.FFLUSH。
典型的使用方法包括分析执行进度和调试纪录。
UTL_FILE.FFLUSH会产生以下异常
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.INVALID_OPERATION
UTL_FILE.WRITE_ERROR
UTL_FILE.FCLOSE
关闭文件
PROCEDUREUTL_FILE.FCLOSE(fileINOUTFILE_TYPE);
file
由FOPEN返回的文件句柄
注意file是一个INOUT参数,因为在关闭文件后会设置为NULL
当试图关闭文件时有缓存数据未写入文件,会抛出WRITE_ERROR异常
UTL_FILE.FCLOSE会产生以下异常
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.WRITE_ERROR
UTL_FILE.FCLOSE_ALL
关闭所有已打开的文件
PROCEDUREUTL_FILE.FCLOSE_ALL;
在结束程序时要确保所有打开的文件已关闭,可使用FCLOSE_ALL
也可以在EXCEPTION使用,当异常退出时,文件也会被关闭。
EXCEPTION
WHENOTHERS
THEN
UTL_FILE.FCLOSE_ALL;
...othercleanupactivities...
END;
注意:
当使用FCLOSE_ALL关闭所有文件时,文件句柄并不会标记为NULL,使用IS_OPEN会返回TRUE。
但是,那些关闭的文件不能执行读写操作(除非你再次打开文件)。
UTL_FILE.FCLOSE_ALL会产生以下异常
UTL_FILE.WRITE_ERROR