UTL_FILE

最新推荐文章于 2023-02-16 15:49:43 发布

原创最新推荐文章于 2023-02-16 15:49:43 发布 · 5.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#file #exception #integer #archive #oracle #sql

本文介绍了如何在Oracle数据库中使用UTL_FILE包进行文件操作，包括创建表、目录对象，读写文件，以及利用UTL_FILE进行文件的复制、删除、重命名和属性查询。示例展示了如何创建存储过程进行文件操作，并强调了在Oracle9i第2版以后，可以使用目录对象来简化路径管理。

SQL > show user
USER 为 "TEST "
SQL > CREATE TABLE test.AAA (
2 T_ID VARCHAR2 (5) NOT NULL,
3 T_IMAGE BLOB NOT NULL
4 );

表已创建。
SQL > create or replace directory images as 'D:/WORKS/DEMO/AA/IMAGES ';

目录已创建。

SQL > CREATE OR REPLACE PROCEDURE test.testp (
2 TID VARCHAR2,
3 FILENAME VARCHAR2) AS
4 F_LOB BFILE;
5 B_LOB BLOB;
6 BEGIN
7 INSERT INTO AAA (T_ID, T_IMAGE) VALUES (TID, EMPTY_BLOB ()) RETURN T_IMAGE INTO B_LOB;
8 F_LOB:= BFILENAME ( 'IMAGES ', FILENAME);
9 DBMS_LOB.FILEOPEN (F_LOB, DBMS_LOB.FILE_READONLY);
10 DBMS_LOB.LOADFROMFILE (B_LOB, F_LOB, DBMS_LOB.GETLENGTH (F_LOB));
11 DBMS_LOB.FILECLOSE (F_LOB);
12 COMMIT;
13 END;
14 /

过程已创建。

SQL >
SQL > execute testp( 'F1001 ', 'AALOGO.GIF ');

PL/SQL 过程已成功完成。

http://www.oracle.com/technology/sample_code/tech/pl_sql/htdocs/x/Utl_File/start.htm

UTL_FILE.FREMOVE 删除文件。
UTL_FILE.FRENAME 重命名文件，事实上，还可以移动文件。
UTL_FILE.FCOPY 将一个文件的全部或部分复制到另一个文件中。
UTL_FILE.FGETATTR获取文件的长度之类的属性。

在Oracle9i第2版之前，当调用FOPEN打开文件（用于读或写）时，UTL_FILE要求必须明确指出文件的位置。这不是最优的实施，因为它意味着开发人员必须在应用程序中的多个地方对那些位置进行硬编码（hard-code）。如果目录改变了，就必须进行很多麻烦的整理工作。

你现在可以在Oracle9i第2版中为目录的文件系统位置指定一个Oracle目录对象的名字。这种技巧"隐藏"了实际的操作系统位置。如果那个位置需要改变，只需要更新目录对象的定义，所有对FOPEN，FREMOVE，FRENAME和FCOPY的调用都不会受到影响。

为了创建一个目录对象，需要具备CREATE ANY DIRECTORY权限，然后就可以像下面的例子那样定义一个新的目录对象：

CREATE OR REPLACE DIRECTORY DEVELOPMENT_
   DIR as '/dev/source';
CREATE OR REPLACE DIRECTORY TEST_DIR 
   as '/test/source';

你应当知道当你创建目录对象时，Oracle数据库并不会验证你所指定的位置。另一点需要留意的是当你在一个调用（如调用UTL_FILE.FOPEN）中指定目录对象的名字时，它被看作一个区分大小写的字符串。换而言之，如果你不是用大写字母指定目录对象的名字，操作将会失败。

在创建了目录对象之后，你可以按下面的方法授权特定的用户使用目录对象：

GRANT READ ON DIRECTORY DEVELOPMENT_DIR 
   to senior_developer;

最后，你可以查询ALL_DIRECTORIES的内容来确定在当前连接的模式中，哪些目录对象可用。这里有一个布尔函数会告诉你指定的目录对象是否可用：

CREATE OR REPLACE FUNCTION dir_available (
   dir_in       IN   VARCHAR2,   
   uppercase_in  IN   BOOLEAN := TRUE
)
   RETURN BOOLEAN
IS 
   v_dir    VARCHAR2 (100) := dir_in;
   retval   BOOLEAN;
   dummy    CHAR (1);
BEGIN
   IF uppercase_in
   THEN

      v_dir := UPPER (v_dir);
   END IF;
   SELECT 'x'
     INTO dummy
     FROM all_directories
    WHERE directory_name = l_dir;
   RETURN TRUE;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN FALSE;
END;

采用这种方法创建实用程序的一个好处是：你可以很轻松地增加关于目录大小写的高级操作，以避免格式错误，如忘记指定目录名字为大写等。

复制、删除和移动文件

在过去，利用UTL_FILE复制文件唯一的方法是编写大量代码来逐行读取一个文件的内容，然后再将其逐行写到一个新的文件。现在，你只需要让UTL_FILE来为你完成这项工作。在清单1中，我用UTL_FILE.FCOPY执行一个选择性的备份--一个从开发目录到存档目录的单一文件的复制。

你也可以用FCOPY仅复制一个文件的一部分。为此，你需要指明文件中希望复制的起始和结束行号。假设我有一个文本文件，其中包含有我儿子的保龄球联盟锦标赛各年冠军的名字。我从1990年开始记录这些名字，并希望将1996年之前的所有名字移到另一个文件。我可以利用下面程序的第5个和第6个参数来完成这个操作：

- Copy just a part of a file
UTL_FILE.fcopy (
   src_location      => 'WINNERS_DIR',

   src_filename      => 'names.txt',
   dest_location     => 'OLD_NEWS_DIR',
   dest_filename     => 'prevnames.txt',
   start_line        => 1,
   end_line          => 6
   );

删除文件也是一件非常容易的事情。假设在将我的zip文件移到存档目录之后，为了释放一些磁盘空间，我希望将它删除。这是部署UTL_FILE.FREMOVE的好机会。注意，在清单2中，我还为新的UTL_FILE.DELETE_FAILED异常定义了一个明确的异常句柄。这种方法使我能对失败的删除操作进行标记（例如，因为我没有所需的权限而导致的失败。）

我还可以通过调用UTL_FILE.FRENAME程序将复制和删除操作合并为一步。这个方便的实用程序使我既能够在相同的目录中重命名文件，也能够对文件的位置和名字都进行重新命名（实际上就是移动文件）。清单3中的例子使用了FRENAME来移动文件archive.zip。

再次说明，当你使用FRENAME时，你应该定义一个异常句柄，它可以十分清楚地捕获重命名失败。

获得一个文件的属性

这个文件有多大？某个特定的文件是否存在？我的文件的块大小是多少？有了操作系统命令的帮助，这些问题不再神秘。UTL_FILE. FGETATTR现在可以在一个本地程序调用中提供所有这些信息。也许利用FGETATTER的最好方法是建立你自己的函数--在内置函数上--来回答一个问题，如清单4中返回一个文件大小（长度）的例子。

有了适当的函数，我现在可以很容易地得到文件的大小，而不必为每个通过FGETATTER得到的属性声明一个变量，如下面的PL/SQL例子：

how_big := flength ('DEVELOPMENT_DIR',
   'all_the_rules.pkg');

这个FLENGTH函数还包括查找一个文件块大小和确定一个文件是否存在的代码。你可以很容易地使用与查找文件长度（大小）相同的技巧（如清单4所示）创建函数，以得到块的大小，并返回一个简单的布尔值来确定文件是否存在。

在写文件时提高了控制能力

UTL_FILE的另一个新功能是PUT_LINE的“自动清洗（auto-flush）”特性。当你在程序中将数据写出到一个文件中时，它不会立即显示在那个文件中，以备读取。操作系统肯定会利用异步I/O，将多个写操作的结果输出到缓冲区，然后再将它们发送到磁盘上。

尽管异步I/O提高了性能，但对于那些需要立即看到一个文件（如日志文件）内容的程序员或支持人员来说，这是一个非常不方便的特性。

现在，UTL_FILE在UTL_FILE.PUT_LINE程序中包括了一个新的参数，以便开发人员可以指定她希望立即输出到磁盘的文本行。清单5中的程序说明了这一技巧。

LISTING 1: Using FCOPY to copy a file

DECLARE
   file_suffix   VARCHAR2 (100)
          := TO_CHAR (SYSDATE, 'YYYYMMDDHHMISS');
BEGIN
   - Copy the entire file...

   UTL_FILE.fcopy (
      src_location      => 'DEVELOPMENT_DIR',
      src_filename      => 'archive.zip',
      dest_location     => 'ARCHIVE_DIR',
      dest_filename     =>    'archive'
                           || file_suffix

                           || '.zip'
   );
END;

LISTING 2: Using FREMOVE to delete a file  
 
BEGIN
   UTL_FILE.fremove (
      src_location     => 'DEVELOPMENT_DIR',
      src_filename     => 'archive.zip'

   );
EXCEPTION
   - If you call FREMOVE, you should check explicitly
   - for deletion failures.
   WHEN UTL_FILE.delete_failed
   THEN

      ... Deal with failure to remove
END;
 
 
LISTING 3: Using FRENAME to move a file  
 
DECLARE
   file_suffix   VARCHAR2 (100)
            := TO_CHAR (SYSDATE, 'YYYYMMDD');
BEGIN
   - Rename/move the entire file in a single step.

   UTL_FILE.frename (
      src_location      => 'DEVELOPMENT_DIR',
      src_filename      => 'archive.zip',
      dest_location     => 'ARCHIVE_DIR',
      dest_filename     =>    'archive'
                           || file_suffix

                           || '.zip',
      overwrite         => FALSE
   );
EXCEPTION
   WHEN UTL_FILE.frename_failed
   THEN
      ... Deal with failure to rename

END;
 
 
LISTING 4: Using FGETATTR as basis for function to find file size  
 
CREATE OR REPLACE FUNCTION flength (
   location_in   IN   VARCHAR2,
   file_in       IN   VARCHAR2
)
   RETURN PLS_INTEGER

IS 
   TYPE fgetattr_t  IS RECORD (
      fexists       BOOLEAN,
      file_length   PLS_INTEGER,
      block_size    PLS_INTEGER
   );


   fgetattr_rec   fgetattr_t;
BEGIN
   UTL_FILE.fgetattr (
      location         => location_in,
      filename         => file_in,
      fexists          => fgetattr_rec.fexists,

      file_length      => fgetattr_rec.file_length,
      block_size       => fgetattr_rec.block_size
   );
   RETURN fgetattr_rec.file_length;
END flength;

LISTING 5: Using PUT_LINE to write to disk immediately  
 
CREATE OR REPLACE PROCEDURE log_error (
   error_in     IN   PLS_INTEGER,
   message_in   IN   VARCHAR2 := NULL
)
IS 

   v_handle              UTL_FILE.file_type;
BEGIN
   v_handle :=
      UTL_FILE.fopen (
         location          => 'ERROR_LOG_DIR',
         filename          => 'error.log',

         open_mode         => 'a',
         max_linesize      => 32767
      );
   UTL_FILE.put_line (
      FILE           => v_handle,
      buffer         =>    'Error '
                        || error_in

                        || ' occurred at '
                        || TO_CHAR (
                              SYSDATE,
                              'YYYYMMDD HH:MI:SS'
                           )
                        || ': '
                        || NVL (
                              message_in,

                              SQLERRM
                           ),
      autoflush      => TRUE
   );
   UTL_FILE.fclose (v_handle);
END;

fopen() 函数使用给定的路径和文件名，新建文件或者打开已有的文件，

这取决于最后一个参数, 当使用@#a@#作为参数时，如果给定的文件不存

在，则以此文件名新建文件，并以写@#w@#方式打开，返回一个文件句柄。

上面代码以天为单位建立日志文件，并且，不同存储过程之间共享log文件，

这种方式的优点是可能通过查看log文件追溯出程序的调用顺序和逻辑。

实际应用中，应根据不同的需求，具体分析，可以使用更复杂的log文件

生成策略。

put_line() 函数用于写入字符到文件，并在字符串的结尾加入换行符，

若不想换行，使用put()函数。

new_line() 函数用于生成指定数目的空行，上面对文件的修改写在一个

缓冲区内，执行fflush() 将立即将buffer中的内容写入文件，当你希

望在文件还未关闭之前就需要读取已经作出的改变时，调用此函数。

is_open() 函数用于判断一个文件句柄的状态，最后用完一定记得把打

开的文件关闭，调用fclose() 函数，并且应把这个语句加入

exception的处理中，防止过程非正常退出时留下未关闭的文件句柄。

CREATE OR REPLACE PROCEDURE XXXXXXX
IS 
cursor c1 is
select * from aaa;
FILE_HANDLE UTL_FILE.FILE_TYPE;
BEGIN
FILE_HANDLE:=UTL_FILE.FOPEN( 'TEMP ','test.txt','W');
for ret_1 in c1
loop
UTL_FILE.put_line(FILE_HANDLE,ret_1.id||','||ret_1.name);
end loop;
UTL_FILE.FCLOSE(FILE_HANDLE);
END;其中
FILE_HANDLE:=UTL_FILE.FOPEN(‘TEMP','test.txt','W');
中的参数分别表示为 目录DIRECTORY,被操作的文件的名字,
对此文件的操作模式(分为R(读),W(覆盖写),A(在已有文件后继续写)
SQL> create or replace directory BILLLOG as 'D:/LOG' ;     
--目录名必须大写，否则 ORA-29280: invalid directory path Directory created. 
在数据库服务器端的相应配置
1、INIT.ORA加入参数UTL_FILE_dir='//youip/ your path '
2、停止oracle的相关服务，点击服务的属性页，选择“登录”tab,
把登陆身份由“本地系统账户”修改成服务器管理员用户、以及相应的密码。
确定后重起服务。把相关的服务都作相应的处理。
3、授予system用户对UTL_FILE包有可执行权限。
GRANT EXECUTE ON utl_file TO DBuser（你的连接数据库的用户）;