Oracle数据库中UTL_FILE_DIR参数详解及操作过程

UTL_FILE_DIR是Oracle中的一个“静态参数”，可以设置一个或多个路径。用于在PL/SQL中进行文件I/O操作（可以用utl_file包）时指定路径。UTL_FILE_DIR是Oracle中的一个“静态参数”，可以设置一个或多个路径。用于在PL/SQL中进行文件I/O操作（可以用utl_file包）时限定路径，utl_file包只能在指定路径下创建，读取文件。utl_file_dir为空时，则不限定路径。

在PL/SQL中没有直接的文件I/O接口，一般在调试程序时可以使用Oracle自带的DBMS_OUTPUT包的put_line函数（即向屏幕进行I/O 操作），但是不能对磁盘文件进行I/O操作。文件I/O对于数据库的开发来说显得很重要，比如如果数据库中的一部分数据来自于磁盘文件，那么就需要使用I/O接口把数据导入到数据库中来。

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.2  UTL_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!