oracle UTL_FILE文件操作

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

1.创建路径

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

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

代码如下：

?

1

2

createorreplace directory BLOB_FILE_DIR as'/home/oracle/export';--linux、unix系统路径(mac最底层核心是unix)，BLOB_FILE_DIR是创建的路径名

createorreplace directory BLOB_FILE_DIR as'D:\test';--windows系统路径

2.授权

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

?

1

2

grantread,writeondirectory BLOB_FILE_DIR totestUesr;--路径授权，添加对路径读、写权限

grantexecuteon utl_file totestUesr;--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

CREATEORREPLACE 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

  SELECTF.C_TEXTINTOL_BUFFERFROMTEST_BLOB F WHEREF.C_ID = I_ID;--随意建一个表，包含<span style="font-family:Arial, Helvetica, sans-serif;">C_TEXT、C_NAME、C_ID字段即可</span>

  SELECTF.C_NAMEINTOL_FILENAMEFROMTEST_BLOB F WHEREF.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

  WHENUTL_FILE.INVALID_PATHTHEN--无效的路径

    DBMS_OUTPUT.PUT_LINE('===INVALID_PATH==='|| I_ID);

    RAISE;

  WHENUTL_FILE.INVALID_MODETHEN--无效的打开模式

    DBMS_OUTPUT.PUT_LINE('===INVALID_MODE==='|| I_ID);

    RAISE;

  WHENUTL_FILE.INVALID_OPERATIONTHEN--无效的操作，文件打开错误会报这个异常，一般来说都是超长或打开方式byte型和非byte型

    DBMS_OUTPUT.PUT_LINE('===INVALID_OPERATION==='|| I_ID);

    RAISE;

  WHENUTL_FILE.INVALID_MAXLINESIZETHEN--无效的最大长度，VARCHAR2最大4000，RAW最大32676，超过回报这个异常，所以一般要进行循环操作

    DBMS_OUTPUT.PUT_LINE('===INVALID_MAXLINESIZE==='|| I_ID);

    RAISE;

  WHENUTL_FILE.ACCESS_DENIEDTHEN--拒绝进入指定路径，可能是授权问题

    DBMS_OUTPUT.PUT_LINE('===ACCESS_DENIED==='|| I_ID);

    RAISE;

  WHENUTL_FILE.INVALID_FILEHANDLETHEN--文件处理错误，不常见

    DBMS_OUTPUT.PUT_LINE('===INVALID_FILEHANDLE==='|| I_ID);

    RAISE;

  WHENUTL_FILE.WRITE_ERRORTHEN--写入错误，处理该异常最好的方式是将要写入的文件简单化，然后找准错误原因

    DBMS_OUTPUT.PUT_LINE('===WRITE_ERROR==='|| I_ID);

    RAISE;

  WHENNO_DATA_FOUNDTHEN--SELECT时候未找到数据，不是UTL_FILE的异常

    DBMS_OUTPUT.PUT_LINE('===NO_DATA_FOUND==='|| I_ID);

    UTL_FILE.FCLOSE(L_FILE);

    RAISE;

  WHENOTHERSTHEN

    IF UTL_FILE.IS_OPEN(L_FILE) THEN

      UTL_FILE.FCLOSE(L_FILE);

      RAISE;

    ENDIF;

ENDGET_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

CREATEORREPLACE 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

      WHENNO_DATA_FOUNDTHEN

        EXIT;

    END;

    DBMS_OUTPUT.PUT_LINE('===IMPORT OK===' || L_BUFFER);

    L_TEXT := L_TEXT || L_BUFFER;

    DBMS_OUTPUT.PUT_LINE('===TEXT OK===' || L_TEXT);

  ENDLOOP;

  INSERTINTOTEST_BLOB F

    (C_ID, C_NAME, C_TEXT)

  VALUES

    (I_ID, I_FILENAME, L_TEXT); --插入表

  UTL_FILE.FCLOSE(L_FILE);

EXCEPTION

  WHENUTL_FILE.INVALID_PATHTHEN

    --无效的路径

    DBMS_OUTPUT.PUT_LINE('===INVALID_PATH==='|| I_ID);

    RAISE;

  WHENUTL_FILE.INVALID_MODETHEN

    --无效的打开模式

    DBMS_OUTPUT.PUT_LINE('===INVALID_MODE==='|| I_ID);

    RAISE;

  WHENUTL_FILE.INVALID_OPERATIONTHEN

    --无效的操作，文件打开错误会报这个异常，一般来说都是超长或打开方式byte型和非byte型

    DBMS_OUTPUT.PUT_LINE('===INVALID_OPERATION==='|| I_ID);

    RAISE;

  WHENUTL_FILE.INVALID_MAXLINESIZETHEN

    --无效的最大长度，VARCHAR2最大4000，RAW最大32676，超过回报这个异常，所以一般要进行循环操作

    DBMS_OUTPUT.PUT_LINE('===INVALID_MAXLINESIZE==='|| I_ID);

    RAISE;

  WHENUTL_FILE.ACCESS_DENIEDTHEN

    --拒绝进入指定路径，可能是授权问题

    DBMS_OUTPUT.PUT_LINE('===ACCESS_DENIED==='|| I_ID);

    RAISE;

  WHENUTL_FILE.INVALID_FILEHANDLETHEN

    --文件处理错误，不常见

    DBMS_OUTPUT.PUT_LINE('===INVALID_FILEHANDLE==='|| I_ID);

    RAISE;

  WHENUTL_FILE.WRITE_ERRORTHEN

    --写入错误，处理该异常最好的方式是将要写入的文件简单化，然后找准错误原因

    DBMS_OUTPUT.PUT_LINE('===WRITE_ERROR==='|| I_ID);

    RAISE;

  WHENNO_DATA_FOUNDTHEN

    --SELECT时候未找到数据，不是UTL_FILE的异常

    DBMS_OUTPUT.PUT_LINE('===NO_DATA_FOUND==='|| I_ID);

    UTL_FILE.FCLOSE(L_FILE);

    RAISE;

  WHENOTHERSTHEN

    IF UTL_FILE.IS_OPEN(L_FILE) THEN

      UTL_FILE.FCLOSE(L_FILE);

      RAISE;

    ENDIF;

ENDSET_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;

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