Oracle技术_UTL_FILE包用法详解_写出文件、读入库表

页面导航:首页 > 数据库 > oracle > Oracle技术_UTL_FILE包用法详解_写出文件、读入库表

Oracle技术_UTL_FILE包用法详解_写出文件、读入库表

来源: 作者: 时间:2016-02-16 09:27 【

UTL_FILE是oracle提供的一个标准的工具包,用来读写文件使用。本文通过实例详细讲解该包的用法,实例具体步骤如下:1 创建路径要操作文件,就需要有对应的路径,而oracle中使用路径需

UTL_FILE是oracle提供的一个标准的工具包,用来读写文件使用。本文通过实例详细讲解该包的用法,实例具体步骤如下:

1.创建路径

要操作文件,就需要有对应的路径,而oracle中使用路径需要用到它的一个结构:directories(路径、地址),具体用法如下:

创建需要操作的路径(ORACLE库所在的服务器),注意这里的路径文件夹一定要存在,没有的话手工先创建,不然很多地方使用会出异常。路径中最好不要有中文,部分地方使用的时候会出无法找到路径的异常。

代码如下:

create or replace directory BLOB_FILE_DIR as '/home/oracle/export';--linux、unix路径(mac最底层核心是unix),BLOB_FILE_DIR是创建的路径名
create or replace directory BLOB_FILE_DIR as 'D:\test';--系统路径

2.授权

将该目录和UTL_FILE包授权给所需用户,注意授权一定要做,不然会无法使用。
代码如下:

grant read,write on directory BLOB_FILE_DIR to testUesr;--路径授权,添加对路径读、写权限
grant execute on utl_file to testUesr;--utl_file包授权,添加执行权限

3.写出文件

使用utl_file写出文件,通过查询库中内容,写出到指定服务器路径下,总体过程如下:
(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方法关闭文件,结束写出。
我们看一下具体操作,这里要创建一个存储过程GET_TEST_BLOB来演示该功能,具体看里面的注释。
代码如下:

CREATE OR REPLACE PROCEDURE GET_TEST_BLOB(I_ID VARCHAR2) IS
  L_FILE     UTL_FILE.FILE_TYPE;
  L_BUFFER   VARCHAR2(4000);--VARCHAR2最长4000,所以超过的话应该使用循环的方式或者用RAW,最长到32676
  L_FILENAME VARCHAR2(300);
BEGIN
  SELECT F.C_TEXT INTO L_BUFFER FROM TEST_BLOB F WHERE F.C_ID = I_ID;--随意建一个表,包含<span style="font-family:Arial, Helvetica, sans-serif;">C_TEXT、C_NAME、C_ID字段即可</span>
  SELECT F.C_NAME INTO L_FILENAME FROM TEST_BLOB F WHERE F.C_ID = I_ID;
  L_FILE := UTL_FILE.FOPEN('BLOB_FILE_DIR', L_FILENAME, 'w');
  --第三个参数为打开模式,包括'r', 'w', 'a' 'rb', 'wb', 'ab'六种
  --'r':读文件,一定要保证有该文件,不然会报UTL_FILE.INVALID_PATH异常
  --'w':写文件,没有该文件的话会自动添加;有的话会覆盖
  --'a':追加文件,一定要保证有该文件,在已有文件内容后追加内容
  --带有'b'后缀的为使用byte模式,BLOB与VARCHAR2不一样,BLOB打开时一定要用带有'b'后缀的模式
  DBMS_OUTPUT.PUT_LINE('===OPEN OK===' || L_FILENAME || '===' ||
                       LENGTH(L_BUFFER) || '===' || L_BUFFER);
  UTL_FILE.PUT_LINE(L_FILE, L_BUFFER);--写入文件
  DBMS_OUTPUT.PUT_LINE('===EXPORT OK===');
  UTL_FILE.FCLOSE(L_FILE);
EXCEPTION
  WHEN UTL_FILE.INVALID_PATH THEN--无效的路径
    DBMS_OUTPUT.PUT_LINE('===INVALID_PATH===' || I_ID);
    RAISE;
  WHEN UTL_FILE.INVALID_MODE THEN--无效的打开模式
    DBMS_OUTPUT.PUT_LINE('===INVALID_MODE===' || I_ID);
    RAISE;
  WHEN UTL_FILE.INVALID_OPERATION THEN--无效的操作,文件打开错误会报这个异常,一般来说都是超长或打开方式byte型和非byte型
    DBMS_OUTPUT.PUT_LINE('===INVALID_OPERATION===' || I_ID);
    RAISE;
  WHEN UTL_FILE.INVALID_MAXLINESIZE THEN--无效的最大长度,VARCHAR2最大4000,RAW最大32676,超过回报这个异常,所以一般要进行循环操作
    DBMS_OUTPUT.PUT_LINE('===INVALID_MAXLINESIZE===' || I_ID);
    RAISE;
  WHEN UTL_FILE.ACCESS_DENIED THEN--拒绝进入指定路径,可能是授权问题
    DBMS_OUTPUT.PUT_LINE('===ACCESS_DENIED===' || I_ID);
    RAISE;
  WHEN UTL_FILE.INVALID_FILEHANDLE THEN--文件处理错误,不常见
    DBMS_OUTPUT.PUT_LINE('===INVALID_FILEHANDLE===' || I_ID);
    RAISE;
  WHEN UTL_FILE.WRITE_ERROR THEN--写入错误,处理该异常最好的方式是将要写入的文件简单化,然后找准错误原因
    DBMS_OUTPUT.PUT_LINE('===WRITE_ERROR===' || I_ID);
    RAISE;
  WHEN NO_DATA_FOUND THEN--SELECT时候未找到数据,不是UTL_FILE的异常
    DBMS_OUTPUT.PUT_LINE('===NO_DATA_FOUND===' || I_ID);
    UTL_FILE.FCLOSE(L_FILE);
    RAISE;
  WHEN OTHERS THEN
    IF UTL_FILE.IS_OPEN(L_FILE) THEN
      UTL_FILE.FCLOSE(L_FILE);
      RAISE;
    END IF;
END GET_TEST_BLOB;<span style="font-family:SimSun;font-size:12px;">
</span>

传入参数,调用该存储过程。
代码如下:

begin
  -- Call the procedure
  get_test_blob('T1');
end;

执行后会在对应目录下生成文件,如下图:

\

 

4.读入文件

使用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方法关闭文件,结束读入。
我们看一下具体操作,这里要创建一个存储过程SET_TEST_BLOB来演示该功能,具体看里面的注释。
CREATE OR REPLACE PROCEDURE SET_TEST_BLOB(I_FILENAME VARCHAR2,
                                          I_ID       VARCHAR2) IS
  L_FILE   UTL_FILE.FILE_TYPE;
  L_BUFFER VARCHAR2(4000); --VARCHAR2最长4000,所以超过的话应该使用循环的方式或者用RAW,最长到32676
  L_TEXT   VARCHAR2(4000) := '';
BEGIN
  L_FILE := UTL_FILE.FOPEN('BLOB_FILE_DIR', I_FILENAME, 'r'); --文件超过4000要使用循环或者RAW(最大32676)
  DBMS_OUTPUT.PUT_LINE('===OPEN OK===' || I_FILENAME);
  LOOP
    BEGIN
      UTL_FILE.GET_LINE(L_FILE, L_BUFFER); --读出文件,最大32676,循环读取,直到报NO_DATA_FOUND异常终止循环
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        EXIT;
    END;
    DBMS_OUTPUT.PUT_LINE('===IMPORT OK===' || L_BUFFER);
    L_TEXT := L_TEXT || L_BUFFER;
    DBMS_OUTPUT.PUT_LINE('===TEXT OK===' || L_TEXT);
  END LOOP;
  INSERT INTO TEST_BLOB F
    (C_ID, C_NAME, C_TEXT)
  VALUES
    (I_ID, I_FILENAME, L_TEXT); --插入表
  UTL_FILE.FCLOSE(L_FILE);
EXCEPTION
  WHEN UTL_FILE.INVALID_PATH THEN
    --无效的路径
    DBMS_OUTPUT.PUT_LINE('===INVALID_PATH===' || I_ID);
    RAISE;
  WHEN UTL_FILE.INVALID_MODE THEN
    --无效的打开模式
    DBMS_OUTPUT.PUT_LINE('===INVALID_MODE===' || I_ID);
    RAISE;
  WHEN UTL_FILE.INVALID_OPERATION THEN
    --无效的操作,文件打开错误会报这个异常,一般来说都是超长或打开方式byte型和非byte型
    DBMS_OUTPUT.PUT_LINE('===INVALID_OPERATION===' || I_ID);
    RAISE;
  WHEN UTL_FILE.INVALID_MAXLINESIZE THEN
    --无效的最大长度,VARCHAR2最大4000,RAW最大32676,超过回报这个异常,所以一般要进行循环操作
    DBMS_OUTPUT.PUT_LINE('===INVALID_MAXLINESIZE===' || I_ID);
    RAISE;
  WHEN UTL_FILE.ACCESS_DENIED THEN
    --拒绝进入指定路径,可能是授权问题
    DBMS_OUTPUT.PUT_LINE('===ACCESS_DENIED===' || I_ID);
    RAISE;
  WHEN UTL_FILE.INVALID_FILEHANDLE THEN
    --文件处理错误,不常见
    DBMS_OUTPUT.PUT_LINE('===INVALID_FILEHANDLE===' || I_ID);
    RAISE;
  WHEN UTL_FILE.WRITE_ERROR THEN
    --写入错误,处理该异常最好的方式是将要写入的文件简单化,然后找准错误原因
    DBMS_OUTPUT.PUT_LINE('===WRITE_ERROR===' || I_ID);
    RAISE;
  WHEN NO_DATA_FOUND THEN
    --SELECT时候未找到数据,不是UTL_FILE的异常
    DBMS_OUTPUT.PUT_LINE('===NO_DATA_FOUND===' || I_ID);
    UTL_FILE.FCLOSE(L_FILE);
    RAISE;
  WHEN OTHERS THEN
    IF UTL_FILE.IS_OPEN(L_FILE) THEN
      UTL_FILE.FCLOSE(L_FILE);
      RAISE;
    END IF;
END SET_TEST_BLOB;<span style="font-family:SimSun;font-size:12px;">
</span>

传入参数,调用该存储过程,注意执行后一定要提交,不然是不会真正插入的。
代码如下:

begin
  -- Call the procedure
  set_test_blob('set_test.txt','ST2');
end;

执行后会将文件内容存入表中,如下图:

\
点击进入ooppookid的博客
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<