Oracle UTLFILE的使用.docx

1、Oracle UTLFILE的使用Oracle UTL_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参数指定的

2、目录列表中，或者指定一个directory对象。否则fopen()会报错。-*指定路径的两种方式*1）utl_file_dir设置这个参数必须重起数据库，可以指定多个目录（用逗号隔开）。如果指定为*，表示任意目录。alter system set utl_file_dir=/u01/oracle,/u02/oracle scope=spfile;2）directory创建一个directory对象并赋予所有用户读写权利create or replace directory logfile_target as /u01/oracle;grant read,write on directory l

3、ogfile_target to public;察看已有的directory对象select * from dba_directories;*-批注（关于directory）Oracle创建directory一般创建directory都是为了用数据泵导入/导出数据用，其实directory还有很多别的用处，本文不做阐述1、新建directory的语法CREATE OR REPLACE DIRECTORY directory AS pathname;例如：create or replace directory dump_dir as D:dumpdir这样把目录d:dumpdir设置成dump_

4、dir代表的directory2、查询有哪些directoryselect * from dba_directories3、赋权grant read,write on directory dump_dir to user014、删除drop directory dump_dir-创建目录， 在可以在oracle中使用目录比如通过pl/sql向目录中写文件，外部表，数据泵，bfile类型都会使用到。oralce内部用来识别OS文件系统路径用的。比如在plsql中进行log输出，制定具体文件路径时要使用到。*3）使用utl_file包输出文件DECLAREl_file utl_file.file_

5、type;-定义一个文件句柄BEGINl_file := utl_file.fopen(LOGFILE_TARGET, sql.log, W);for row in (select * from user_tables)looputl_file.put_line(l_file, dbms_metadata.get_ddl(TABLE, row.table_name);end loop;utl_file.fclose(l_file);END;*这里也可以写成utl_file.fopen(/u01/oracle, sql.log, W);*fopen()的第三个参数OPEN_MODE，可以指定为r

6、 - read textw - write texta - append textrb - read byte modewb - write byte modeab - append byte mode如果指定a或者ab但是文件不存在会先创建。Oracle的utl_file来读取txt文件Oracle的utl_file来读取txt文件，自己参考了两个实例，自己也动手稍微写了一下，来和大家分享一下，不是太完整，希望大家多多指点。工具/原料 本人是在C盘下面建了一个test.txt的文件，内容如下：hello oracle!你好，胖子! 还有一个test表CREATE TABLE TEST(a V

7、ARCHAR(30),b VARCHAR(30);方法/步骤1. 1创建一个directory:create or replace directory FILENAME as C:TEMP;2. 2往文件里面写内容：-写txt文件declare filehandle utl_file.file_type;-句柄beginfilehandle := utl_file.fopen(路径名,test1.txt,w);-（路径名，文件名，读写方式）utl_file.put_line(filehandle,hello oracle!);utl_file.put_line(filehandle,你好，胖子

8、!);utl_file.fclose(filehandle);-关闭句柄end;3. 3读取txt文件中的内容，并写入到表中：-读取txt文件/*set serveroutput ON*/DECLARE filehandle utl_file.file_type; filebuffer varchar(200); BEGIN filehandle := utl_file.fopen(FILENAME,test1.txt,r); loop begin utl_file.get_line(filehandle,filebuffer); INSERT INTO TEST(a) VALUES(file

9、buffer); EXCEPTION WHEN no_data_found THEN exit ; End; END LOOP; utl_file.fclose(filehandle);COMMIT;END;4. 4演示结果：如图select * from test;5. 5注意：filehandle := utl_file.fopen(FILENAME,test1.txt,w);此处的FILENAME必须为大写，否则会提示：ERROR 位于第 1 行:ORA-29280: 目录路径无效ORA-06512: 在SYS.UTL_FILE, line 18ORA-06512: 在SYS.UTL_F

10、ILE, line 424ORA-06512: 在line 4END注意事项 filehandle := utl_file.fopen(FILENAME,test1.txt,w);此处的FILENAME必须为大写，否则会提示：ERROR 位于第 1 行:ORA-29280: 目录路径无效ORA-06512: 在SYS.UTL_FILE, line 18ORA-06512: 在SYS.UTL_FILE, line 424ORA-06512: 在line 4 要是你试着不好用的话，就自己新开一个sql widnow对话框，运行程序即可utl_file包的使用（重要-）包中主要的函数FOPENIS_

11、OPENGET_LINEPUTNEW_LINEPUT_LINEPUTFFFLUSHFCLOSEFCLOSE_ALL使用utl_file包之前应先建立目录create or replace directory BFILE_DIR asf:/home/oracle/bfiletest;给用户读写该目录的权限grant read，write on directory BFILE_DIR to lunar;GRANT EXECUTE ON utl_file TO wangyhUTL_FILE.FOPEN用法FOPEN会打开指定文件并返回一个文件句柄用于操作文件。 FUNCTION UTL_FILE.F

12、OPEN ( location IN VARCHAR2, filename IN VARCHAR2, open_mode IN VARCHAR2, max_linesize IN BINARY_INTEGER) RETURN file_type;参数location文件地址filename文件名openmode打开文件的模式（参见下面说明）max_linesize文件每行最大的字符数，包括换行符。最小为1，最大为327673种文件打开模式：R 只读模式。一般配合UTL_FILE的GET_LINE来读文件。W 写（替换）模式。文件的所有行会被删除。PUT, PUT_LINE, NEW_LINE,

13、 PUTF和FFLUSH都可使用A 写（附加）模式。原文件的所有行会被保留。在最末尾行附加新行。PUT, PUT_LINE, NEW_LINE, PUTF和FFLUSH都可使用打开文件时注意以下几点：文件路径和文件名合起来必须表示操作系统中一个合法的文件。文件路径必须存在并可访问；FOPEN并不会新建一个文件夹。如果你想打开文件进行读操作，文件必须存在；如果你想打开文件进行写操作，文件不存在时，会新建一个文件。如果你想打开文件进行附加操作，文件必须存在。A模式不同于W模式。文件不存在时，会抛出INVALID_OPERATION异常。FOPEN 会抛出以下异常UTL_FILE.INVALID_M

14、ODEUTL_FILE.INVALID_OPERATIONUTL_FILE.INVALID_PATHUTL_FILE.INVALID_MAXLINESIZEUTL_FILE.IS_OPEN用法如果文件句柄指定的文件已打开，返回TRUE，否则FALSEFUNCTION UTL_FILE.IS_OPEN (file IN UTL_FILE.FILE_TYPE) RETURN BOOLEAN;UTL_FILE只提供一个方法去读取数据：GET_LINEUTL_FILE.GET_LINE用法读取指定文件的一行到提供的缓存。PROCEDURE UTL_FILE.GET_LINE (file IN UTL_

15、FILE.FILE_TYPE, buffer OUT VARCHAR2);file由FOPEN返回的文件句柄buffer读取的一行数据的存放缓存buffer必须足够大。否则，会抛出VALUE_ERROR 异常。行终止符不会被传进buffer。异常NO_DATA_FOUNDVALUE_ERRORUTL_FILE.INVALID_FILEHANDLEUTL_FILE.INVALID_OPERATIONUTL_FILE.READ_ERRORUTL_FILE.PUT用法在当前行输出数据PROCEDURE UTL_FILE.PUT (file IN UTL_FILE.FILE_TYPE, buffer

16、OUT VARCHAR2);file由FOPEN返回的文件句柄buffer包含要写入文件的数据缓存；Oracle8.0.3及以上最大允许32kB，早期版本只有1023BUTL_FILE.PUT输出数据时不会附加行终止符。UTL_FILE.PUT会产生以下异常UTL_FILE.INVALID_FILEHANDLEUTL_FILE.INVALID_OPERATIONUTL_FILE.WRITE_ERRORUTL_FILE.NEW_LINE-在当前位置输出新行或行终止符，（新建一个空行）必须使用NEW_LINE来结束当前行，或者使用PUT_LINE输出带有行终止符的完整行数据。PROCEDURE U

17、TL_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_ERRORUTL_FILE.INVALID_FILEHANDLEUTL_FILE.INVALID_OPERATIONUTL_FILE.WR

18、ITE_ERROR例子如果要在UTL_FILE.PUT后立刻换行，可以如下例所示：PROCEDURE add_line (file_in IN UTL_FILE.FILE_TYPE, line_in IN VARCHAR2)ISBEGIN 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);

19、file由FOPEN返回的文件句柄buffer包含要写入文件的数据缓存；Oracle8.0.3及以上最大允许32kB，早期版本只有1023B在调用UTL_FILE.PUT_LINE前，必须先打开文件。UTL_FILE.PUT_LINE会产生以下异常UTL_FILE.INVALID_FILEHANDLEUTL_FILE.INVALID_OPERATIONUTL_FILE.WRITE_ERROR例子这里利用UTL_FILE.PUT_LINE从表emp读取数据到文件：declare fileID UTL_FILE.FILE_TYPE;BEGIN fileID := UTL_FILE.FOPEN (B

20、FILE_DIR, emp.TXT, W); /* Quick and dirty construction here! */ FOR emprec IN (SELECT * FROM emp) LOOP UTL_FILE.PUT_LINE (FILEID,TO_CHAR (emprec.empno) | , | emprec.ename | , | TO_CHAR (emprec.deptno); END LOOP; UTL_FILE.FCLOSE (fileID);END;/GRANT READ ON DIRECTORY BFILE_DIR TO SCOTT;GRANT write ON

21、DIRECTORY BFILE_DIR TO SCOTT;PUT_LINE相当于PUT后加上NEW_LINE；也相当于PUTF的格式串%sn。UTL_FILE.PUTF以一个模版样式输出至多5个字符串，类似C中的printfPROCEDURE 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 ,

22、arg5 IN VARCHAR2 DEFAULT NULL);file由FOPEN返回的文件句柄format决定格式的格式串argN可选的5个参数，最多5个格式串可使用以下样式%s在格式串中可以使用最多5个%s，与后面的5个参数一一对应n换行符。在格式串中没有个数限制%s会被后面的参数依次填充，如果没有足够的参数，%s会被忽视，不被写入文件UTL_FILE.PUTF会产生以下异常UTL_FILE.INVALID_FILEHANDLEUTL_FILE.INVALID_OPERATIONUTL_FILE.WRITE_ERRORdeclare fileID UTL_FILE.FILE_TYPE;BE

23、GIN fileID := UTL_FILE.FOPEN (BFILE_DIR, emp.TXT, W); /* Quick and dirty construction here! */ FOR emprec IN (SELECT * FROM emp) LOOP UTL_FILE.PUTf (FILEID,TO_CHAR (emprec.empno) | , | emprec.ename | , | TO_CHAR (emprec.deptno),);utl_file.new_line(fileid); END LOOP; UTL_FILE.FCLOSE (fileID);END;/UTL

24、_FILE.FFLUSH确保所有数据写入文件。PROCEDURE UTL_FILE.FFLUSH (file IN UTL_FILE.FILE_TYPE);file由FOPEN返回的文件句柄操作系统可能会缓存数据来提高性能。因此可能调用put后，打开文件却看不到写入的数据。在关闭文件前要读取数据的话可以使用UTL_FILE.FFLUSH。典型的使用方法包括分析执行进度和调试纪录。UTL_FILE.FFLUSH会产生以下异常UTL_FILE.INVALID_FILEHANDLEUTL_FILE.INVALID_OPERATIONUTL_FILE.WRITE_ERRORUTL_FILE.FCLOS

25、E关闭文件PROCEDURE UTL_FILE.FCLOSE (file IN OUT FILE_TYPE);file由FOPEN返回的文件句柄注意file是一个IN OUT参数，因为在关闭文件后会设置为NULL当试图关闭文件时有缓存数据未写入文件，会抛出WRITE_ERROR异常UTL_FILE.FCLOSE会产生以下异常UTL_FILE.INVALID_FILEHANDLEUTL_FILE.WRITE_ERRORUTL_FILE.FCLOSE_ALL关闭所有已打开的文件PROCEDURE UTL_FILE.FCLOSE_ALL;在结束程序时要确保所有打开的文件已关闭，可使用FCLOSE_ALL也可以在EXCEPTION使用，当异常退出时，文件也会被关闭。EXCEPTION WHEN OTHERSTHEN UTL_FILE.FCLOSE_ALL; . other clean up activities .END;注意：当使用FCLOSE_ALL关闭所有文件时，文件句柄并不会标记为NULL，使用IS_OPEN会返回TRUE。但是，那些关闭的文件不能执行读写操作（除非你再次打开文件）。UTL_FILE.FCLOSE_ALL会产生以下异常UTL_FILE.WRITE_ERROR