ITPub博客

首页 > 应用开发 > IT综合 > Utl_file文章使用

Utl_file文章使用

原创 IT综合 作者:jixuewen 时间:2007-12-11 14:33:49 0 删除 编辑

最近用到了Oracle的包UTL_FILE,网上却没找到关于它的函数,过程使用说明,虽然都不是很难的东西,但简单列出来,也能提高些效率。
于是有了这篇文。
以下翻译来自《Oracle Built-in Packages》的第六章,只翻译了部分,想了解的更详细,请参考原文。http://www.oreilly.com/catalog/oraclebip/chapter/ch06.html

FOPEN
IS_OPEN
GET_LINE
PUT
NEW_LINE
PUT_LINE
PUTF
FFLUSH
FCLOSE
FCLOSE_ALL

UTL_FILE.FOPEN 用法
FOPEN会打开指定文件并返回一个文件句柄用于操作文件。
所有PL/SQL版本: Oracle 8.0版及以上:
FUNCTION UTL_FILE.FOPEN ( FUNCTION UTL_FILE.FOPEN (
location IN VARCHAR2, location IN VARCHAR2,
filename IN VARCHAR2, filename IN VARCHAR2,
open_mode IN VARCHAR2) open_mode IN VARCHAR2,
RETURN file_type; max_linesize IN BINARY_INTEGER)
RETURN file_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

FUNCTION UTL_FILE.IS_OPEN (file IN UTL_FILE.FILE_TYPE) RETURN BOOLEAN;

UTL_FILE只提供一个方法去读取数据:GET_LINE

UTL_FILE.GET_LINE用法
读取指定文件的一行到提供的缓存。
PROCEDURE UTL_FILE.GET_LINE
(file IN UTL_FILE.FILE_TYPE,
buffer OUT VARCHAR2);

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用法
在当前行输出数据
PROCEDURE UTL_FILE.PUT
(file IN UTL_FILE.FILE_TYPE,
buffer OUT VARCHAR2);
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输出带有行终止符的完整行数据。

PROCEDURE UTL_FILE.NEW_LINE
(file IN UTL_FILE.FILE_TYPE,
lines IN NATURAL := 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后立刻换行,可以如下例所示:
PROCEDURE add_line (file_in IN UTL_FILE.FILE_TYPE, line_in IN VARCHAR2)
IS
BEGIN
UTL_FILE.PUT (file_in, line_in);
UTL_FILE.NEW_LINE (file_in);
END;


UTL_FILE.PUT_LINE
输出一个字符串以及一个与系统有关的行终止符
PROCEDURE UTL_FILE.PUT_LINE
(file IN UTL_FILE.FILE_TYPE,
buffer IN VARCHAR2);
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读取数据到文件:
PROCEDURE emp2file
IS
fileID UTL_FILE.FILE_TYPE;
BEGIN
fileID := UTL_FILE.FOPEN ('/tmp', 'emp.dat', 'W');

/* Quick and dirty construction here! */
FOR emprec IN (SELECT * FROM emp)
LOOP
UTL_FILE.PUT_LINE
(TO_CHAR (emprec.empno) || ',' ||
emprec.ename || ',' ||
...
TO_CHAR (emprec.deptno));
END LOOP;

UTL_FILE.FCLOSE (fileID);
END;
PUT_LINE相当于PUT后加上NEW_LINE;也相当于PUTF的格式串"%sn"。

UTL_FILE.PUTF以一个模版样式输出至多5个字符串,类似C中的printf

PROCEDURE UTL_FILE.PUTF
(file IN FILE_TYPE
,format IN VARCHAR2
,arg1 IN VARCHAR2 DEFAULT NULL
,arg2 IN VARCHAR2 DEFAULT NULL
,arg3 IN VARCHAR2 DEFAULT NULL
,arg4 IN VARCHAR2 DEFAULT NULL
,arg5 IN VARCHAR2 DEFAULT NULL);
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

UTL_FILE.FFLUSH确保所有数据写入文件。
PROCEDURE UTL_FILE.FFLUSH (file IN UTL_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
关闭文件
PROCEDURE UTL_FILE.FCLOSE (file IN OUT FILE_TYPE);
file
由FOPEN返回的文件句柄

注意file是一个IN OUT参数,因为在关闭文件后会设置为NULL
当试图关闭文件时有缓存数据未写入文件,会抛出WRITE_ERROR异常

UTL_FILE.FCLOSE会产生以下异常
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.WRITE_ERROR

UTL_FILE.FCLOSE_ALL
关闭所有已打开的文件
PROCEDURE UTL_FILE.FCLOSE_ALL;

在结束程序时要确保所有打开的文件已关闭,可使用FCLOSE_ALL
也可以在EXCEPTION使用,当异常退出时,文件也会被关闭。
EXCEPTION
WHEN OTHERS

THEN
UTL_FILE.FCLOSE_ALL;
... other clean up activities ...
END;

注意:当使用FCLOSE_ALL关闭所有文件时,文件句柄并不会标记为NULL,使用IS_OPEN会返回TRUE。但是,那些关闭的文件不能执行读写操作(除非你再次打开文件)。
UTL_FILE.FCLOSE_ALL会产生以下异常
UTL_FILE.WRITE_ERROR

另一篇关于utl_file的文章:

第一步:以管理员用户登陆
如:conn sys/password@sid as sysdba
第二步:设置可操作目录
需要指定utl_file包可以操作的目录。在oracle 10g以前,可以用以下方法:
1、alter system set utl_file_dir='e:utl' scope=spfile;
2、在init.ora文件中,配置如下:
UTL_FILE=E:utl或者UTL_FILE_DIR=E:utl
在oracle 10g中建议用以下方法配置:CREATE DIRECTORY utl AS 'E:utl';
参见oracle online:
In the past, accessible directories for the UTL_FILE functions were specified in the initialization file using the UTL_FILE_DIR parameter. However, UTL_FILE_DIR access is not recommended. It is recommended that you use the CREATE DIRECTORY feature, which replaces UTL_FILE_DIR. Directory objects offer more flexibility and granular control to the UTL_FILE application administrator, can be maintained dynamically (that is, without shutting down the database), and are consistent with other Oracle tools. CREATE DIRECTORY privilege is granted only to SYS and SYSTEM by default.

第三步:授权给指定用户,以便执行utl_file
GRANT EXECUTE ON utl_file TO scott;

第四步:conn scott/tiger
就可以正常使用utl_file了。




摘要:本文主要讨论如何利用Oracle的UTL_FILE包来实现对磁盘文件的I/O操作。

  文件I/O对于数据库的开发来说显得很重要,比如如果数据库中的一部分数据来自于磁盘文件,
那么就需要使用I/O接口把数据导入到数据库中来。在 PL/SQL中没有直接的I/O接口,
一般在调试程序时可以使用Oracle自带的DBMS_OUTPUT包的put_line函数(即向屏幕进行I/O 操作)即可,
但是对于磁盘文件的I/O操作它就无能为力了。其实Oracle同样也提供了可以进行文件I/O的实用包-----UTL_FILE包,
利用这个实用包提供的函数来实现对磁盘的I/O操作。

  1. 准备工作

  由于Oracle数据库对包创建的目录有一个安全管理的问题,所以并不是所有的文件目录能够被UTL_FILE包所访问,
要更新这种目录设置,就得到init.ora里将UTL_FILE_DIR域设置为*,这样UTL_FILE包就可以对所有的目录文件进行访问了。

  2. 文件I/O的实施

  UTL_FILE包提供了很多实用的函数来进行I/O操作,主要有以下几个函数:

  fopen

  打开指定的目录路径的文件。

  get_line

  获取指定文件的一行的文本。

  put_line

  向指定的文件写入一行文本。

  fclose

  关闭指定的文件。

  下面利用这些函数,实现从文件取数据,然后将数据写入到相应的数据库中。

create or replace procedure loadfiledata(p_path varchar2,p_filename varchar2) as
v_filehandle utl_file.file_type; --定义一个文件句柄
v_text varchar2(100); --存放文本
v_name test_loadfile.name%type;
v_addr_jd test_loadfile.addr_jd%type;
v_region test_loadfile.region%type;
v_firstlocation number;
v_secondlocation number;
v_totalinserted number;
begin
if (p_path is null or p_filename is null) then
goto to_end;
end if;
v_totalinserted:=0;
/*open specified file*/
v_filehandle:=utl_file.fopen(p_path,p_filename,'r');
loop
begin
utl_file.get_line(v_filehandle,v_text);
exception
when no_data_found then
exit;
end ;
v_firstlocation:=instr(v_text,',',1,1);
v_secondlocation:=instr(v_text,',',1,2);
v_name:=substr(v_text,1,v_firstlocation-1);
v_addr_jd:=substr(v_text,v_firstlocation+1,v_secondlocation-v_firstlocation-1);
v_region:=substr(v_text,v_secondlocation+1);
/*插入数据库操作*/
insert into test_loadfile
values (v_name,v_addr_jd,v_region);
commit;
end loop;
<>
null;
end loadfiledata;



可以不用在init.ora中改的
只要用管理员的权限登陆,执行:
create directory UTL_FILE_TEST as '*'
应该就可以了,不用新启动oracle的
不过其他用户要使用此目录要授权的
/


grant create any directory to scott;
grant create any library to scott;
create or replace directory utllobdir as 'C:ep';


在initsid.ora文件中,加入或修改
设置utl_file_dir的要点:
1。 utl_file_dir=* 这表示你能操作任何目录,尽量不要用
2。 utl_file_dir=d: 这表示你能操作d:目录下的文件,但你不能操作d:目录下的子目录
3。注意在设置
utl_file_dir=路径时,如果路径是长路径名,例如c:my temp目录,则你必须加上'',例如:
utl_file_dir='c:my temp'
4。utl_file_dir可以是多个路径
utl_file_dir=c:,d:,d:temp,'c:my temp'
5。设置完必须重新启动数据库

[@more@]转自:http://www.blogjava.net/liwei/archive/2007/01/10/92902.aspx

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/564597/viewspace-991471/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    95
  • 访问量
    1995303