Oracle 利用 UTL_FILE 内建包实现文件I/O操作

   Oracle本身提供了大量使用的包，如UTL_HTTP包，DBMS_OUTPUT包等，这些包分别封装了不同的功能，它们使得进行大量的应用程序开发的可能，从而拓展了Oracle的功能；

  

   花费了我半个月的时间做DB table的import/Export工作, 对于exp为cvs格式文件, 我使用.net项目中的控件做的,但是也有个缺陷, 如果说有成百万的数据要导出的话, 就绝对timeout; 对于imp, 我使用.net 提供来对 oracleClient 的 OracleCommandBuilder 对象进行操作, 如下:

 

        Dim objBuilder As OracleCommandBuilder = New OracleCommandBuilder(dbda)
        dbda.UpdateCommand = objBuilder.GetUpdateCommand
        dbda.InsertCommand = objBuilder.GetInsertCommand

 

dbda.Update(dbds.Tables(tbname).Select(Nothing, Nothing, DataViewRowState.CurrentRows))

 

这样做的原理是: 先把数据源csv file中的数据load到dt1中保存, 然后得到DB中table的框架dt2后, 再把dt1中的数据填充到dt2中(可理解为追加数据到DB table), dbds 即就是填充了csv数据的DataSet, 利用上面的命令即可把csv数据更新到DB中；但是这样有一个不足, 即是事先必须完成对csv数据的合法性检查。

 

  基于以上矛盾与不足, 别的部门同事建议我试试Oracle 拓展包UTL, 我查了查资料发现这个内建包可以直接操作文件的读写, 甚至可以生成文件。

 

 1. 需要在DB server上设置如下:

   Create or replace Directory BLUESHARE as 'D:/TEMP';

   grant read, write on Directory BLUESHARE to BSDEV;

   grant execute on utl_file to BSDEV;

 

PS：'D:/TEMP' 是对应Oracle可以操作访问的文件夹(Window server 2003 or Linux);

       BSDEV-->Oracle instance DB;

       之后就可以用UTL_FILE包建立文件了;

 

UTL_FILE.FOPEN(location in varchar2, filename in varchar2, open_mode in varchar2, 32767) return FILE_TYPE;
Location 是路径参数(可使用建立的文件夹别名, 如 'BLUESHARE')，
FILENAME 是文件名(纯粹的文件名)，
OPEN_MODE是打开模式，'R'是读文本，'W'是写文本，'A'是附加文本，参数不分大小写，如果指定'A'但是文件不存在，它会用'W'先创建出来，'W'有覆盖的功能；

32767: 是指缓冲区的大小;

其中的location并不能简单的指定为'D:/temp'等路径，要建立一个DIRECTORY变量并付给权限(必须以DBA身份登录),
UTL包打开BLUESHARE文件夹下的csv数据文件后, 会一行一行去读取供user操作;

 

eg: 我读取了一行csv数据后, 其是以逗号(',')分隔每个Column的, 因此接下来我要用逗号把数据分隔后存入DB array类型的变量, 代码如下:

 

CREATE OR REPLACE FUNCTION BSDEV.fn_split_string (
   STRING      VARCHAR2,
   substring   VARCHAR2
)
   RETURN varchar2varray
IS
   len       INTEGER        := LENGTH (substring);
   lastpos   INTEGER        := 1 - len;
   pos       INTEGER;
   num       INTEGER;
   i         INTEGER        := 1;
   ret       varchar2varray := varchar2varray (NULL);
BEGIN
   LOOP
      pos := INSTR (STRING, substring, lastpos + len);

      IF pos > 0
      THEN                                                            --found
         num := pos - (lastpos + len);
      ELSE                                                         --not found
         num := LENGTH (STRING) + 1 - (lastpos + len);
      END IF;

      IF i > ret.LAST
      THEN
         ret.EXTEND;
      END IF;

      ret (i) := SUBSTR (STRING, lastpos + len, num);
      EXIT WHEN pos = 0;
      lastpos := pos;
      i := i + 1;
   END LOOP;

   RETURN ret;
END;
/

 

PS: 之后请create一个特别的数据类型--

REATE OR REPLACE
TYPE         BSDEV.VARCHAR2VARRAY IS VARRAY(100) of VARCHAR2(400);

 

2. 用上述function 得到的结果类型.net里面的数组, 因此可以按索引从中取出相应的数据(每个csv文件CELL的值);

    之后的操作就可以按需要来实现;

 

 另外也可以用UTL包实现生成 csv 或 xls 文件, 此处以成.csv文件为例, xls仅仅将CHR(44)-->CHR(9);

 其中 v_ilehandle ---> 即用 UTL_FILE 打开的文件句柄；

 

UTL_FILE.put_line (v_ilehandle,  '"' || v_sid || '"' || CHR (44)
                                                || '"' || v_plant || '"' || CHR (44)
                                                || '"' || v_customer  || '"' || CHR (44)
                                                || '"' || CHR (44) || '"' || v_creatdt || '"' || CHR (44)
                                                || '"' || v_modifydt|| '"' );

 

上面是往打开的文件中以.csv文件格式写入一行数据;

 

3. 最后以下的语句可以将打开的句柄关闭:

    IF UTL_FILE.is_open (v_filehandle)  THEN
       UTL_FILE.fclose (v_filehandle);
    END IF;

 

4. Exception section:

    可以将Oracle 的错误信息报出(传出给。net调用者), 假定 p_errMsg 是 procedure 的传出vchar2类型的参数;

    p_errMsg := SQLERRM;

 

Web 程式开发后需部署在IIS5.1上, 有浏览器在客户端访问,至于安装IIS 的 Windows 03 跟 Linux 的 共享文件夹BLUESHARE的操作权限可以找强人帮忙唷, 这里you 可以在Webconfig中设置以下:

<identity impersonate="true" userName="userName" password="passWord"/>;

 

Oracle built_in package pls view : http://oreilly.com/catalog/oraclebip/chapter/ch06.html