oracle UTL_FILE文件操作

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

1.创建路径

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

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

代码如下：

?

1 2	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' ; --windows系统路径

2.授权

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

?

1 2	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来演示该功能，具体看里面的注释。
代码如下：

?

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

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>

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

?

1 2 3 4

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来演示该功能，具体看里面的注释。

?

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 61 62 63 64 65

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>

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

?

1 2 3 4

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

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