Oracle技術_UTL_FILE包用法詳解_寫出文件、讀入庫表

Oracle技術_UTL_FILE包用法詳解_寫出文件、讀入庫表

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';--windows系統路徑

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的博客