[原]使用Oracle BFILE 导入文件到数据库一例

最近发现新接手的网站群系统有个非主流的设计——竟然是将文章的正文内容保存在文件中！这导致数据同步和迁移带来了很大的困难，如何将文件的内容重新放进数据库成为了摆在面前的一个问题。

经过搜索，发现Oracle有一种数据类型成为BFILE，可以访问外部的文件，并提供了一系列的过程，例如：dbms_lob.LoadFromFile ， dbms_lob.LoadClobFromFile 还有 dbms_lob.LoadBlobFromFile ， 大概看名字的就能猜到是做什么的了吧。

使用BFILE访问文件需要首先在数据库中建立 directory ，限定访问路径，当然相应的权限也得跟上：

?

1 2	sys$logdw@logserver SQL> create directory cms_data as '/tmp/webapps/cms/cms-data/' ; sys$logdw@logserver SQL> grant read on cms_data to cms ;

我们再看看表结构：

 SQL> desc p_lob_text;  
 Name                   Null?    Type         
 ---------------------- -------- -------------
 LOB_ID                 NOT NULL VARCHAR2(32)
 KEY_INFO                        VARCHAR2(100)
 LOB_CONTENT                     CLOB
 LOB_SIZE                        VARCHAR2(100)
 LINK_TABLE                      VARCHAR2(100)
 LINK_INFO                       VARCHAR2(50)
 LINK_CATEGORY                   VARCHAR2(50)
 CREATION_TIME                   VARCHAR2(30)
 LAST_EDIT_TIME                  VARCHAR2(30)
 VERSION                         NUMBER(38)
 REMARK                          VARCHAR2(500)
 STATIC_FLAG                     VARCHAR2(3)
 DELETE_FLAG                     VARCHAR2(3)

逻辑是这样的，通过lob_id经过变换后可知道文件的位置，然后将该文件导入到对应的clob中。

我写了个过程使用过程、游标进行此操作：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60

declare   l_bfile bfile;   l_clob clob;   l_lob_id varchar(100);   l_path varchar(200);   --   V_SOURCE NUMBER := 1;   V_DEST NUMBER := 1;   V_LANG NUMBER := 0;   V_WARN NUMBER;   --   CURSOR cur is     select __id__     from __tablle_name__ where ; begin   open cur;   loop     fetch cur into l_lob_id;     EXIT when cur%NOTFOUND;     -- 构建文件路径     l_path := '__tablle_name__.'||l_lob_id||'.bin';     -- 有些CLOB是NULL，首先得让它变成 empty clob     update __tablle_name__     set __lob_content_field__=empty_clob     where id=l_lob_id     return __lob_content_field__ INTO l_clob;     -- 创建一个bfile对象     l_bfile := bfilename('IMPDP_DIR',l_path);     -- 判断文件是否存在     if ( dbms_lob.fileexists (l_bfile) = 1 ) then       -- 重设一些参数       V_SOURCE := 1;       V_DEST := 1;       V_LANG := 0;       -- 打开文件       dbms_lob.fileopen(l_bfile);       if (         -- 判断文件是否打开         dbms_lob.fileisopen(l_bfile) = 1         and         -- 判断文件的长度是否为了0         dbms_lob.getlength(l_bfile) >0           ) then          -- 将文件Load到clob字段中         dbms_lob.loadclobfromfile           ( l_clob,             l_bfile,             DBMS_LOB.LOBMAXSIZE ,             V_DEST,             V_SOURCE,             0,V_LANG,V_WARN           );       end if;       -- 打开文件后一定要记得关闭哦       dbms_lob.fileclose(l_bfile);     end if;     commit;   end loop; end; /

对于blob要稍微改一下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

declare   l_directory varchar(200) := '__dir__';     l_bfile bfile;     l_blob blob;     l_lob_id varchar(100);     l_path varchar(200);     V_SOURCE NUMBER := 1;     V_DEST NUMBER := 1;     V_LANG NUMBER := 0;     V_WARN NUMBER;     CURSOR cur is       select __id__ from __table_name__; begin     open cur;     loop         fetch cur into l_lob_id;         EXIT when cur%NOTFOUND;         -- setting the path of file         l_path :=  'blob/adapter_blob.'||to_char(l_lob_id)||'.bin';         update __table_name__ set __blob_field__=empty_blob() where __id__=l_lob_id           return content INTO l_blob;         l_bfile := bfilename( l_directory,l_path);         if ( dbms_lob.fileexists (l_bfile) = 1 ) then             V_SOURCE := 1;             V_DEST := 1;             V_LANG := 0;             dbms_lob.fileopen(l_bfile);             if (                     dbms_lob.fileisopen(l_bfile) = 1                     and                     dbms_lob.getlength(l_bfile) >0 )               then                 dbms_lob.loadblobfromfile                     ( l_blob,                       l_bfile,                       DBMS_LOB.LOBMAXSIZE ,                       V_DEST,                       V_SOURCE                     );             end if;             dbms_lob.fileclose(l_bfile);         end if;         commit;     end loop; end; /