您现在的位置：程式碼之家 >> 編程語言 > >> 更多程式語言 >> 更多程式設計知識

Oracle UTL_FILE 套件實作檔案 I/O：讀取文字檔寫入資料表完整範例

編輯：更多程式設計知識

前置準備

資料庫參數檔 (`init.ora` / `spfile`) 設定 UTL_FILE_DIR = '*' 允許存取所有目錄 (或具體路徑)，**需重啟實例生效**。
建立目標表：

CREATE TABLE test_loadfile (
    name      VARCHAR2(100) NOT NULL,
    addr_jd   VARCHAR2(20),
    region    VARCHAR2(6)
);

核心 API

函數/程序	用途
FOPEN(location, filename, mode) → file_type	開啟檔案，mode: 'r' 讀、'w' 寫、'a' 附加
GET_LINE(file, buffer OUT)	讀取一行 (含換行符)，無資料拋 NO_DATA_FOUND
PUT_LINE(file, buffer)	寫入一行 (自動加換行)
FCLOSE(file) / FCLOSE_ALL	關閉檔案

完整 PL/SQL 程序：載入 CSV 至資料表

CREATE OR REPLACE PROCEDURE loadfiledata(
    p_path     IN VARCHAR2,  -- 目錄路徑，如 'F:\data\'
    p_filename IN VARCHAR2   -- 檔名，如 '地址資訊.txt'
) AS
    v_file   UTL_FILE.FILE_TYPE;
    v_line   VARCHAR2(4000);
    v_name   test_loadfile.name%TYPE;
    v_addr   test_loadfile.addr_jd%TYPE;
    v_region test_loadfile.region%TYPE;
    v_pos1   PLS_INTEGER;
    v_pos2   PLS_INTEGER;
BEGIN
    IF p_path IS NULL OR p_filename IS NULL THEN
        RETURN;
    END IF;

    v_file := UTL_FILE.FOPEN(p_path, p_filename, 'r', 32767);  -- max linesize

    LOOP
        BEGIN
            UTL_FILE.GET_LINE(v_file, v_line);
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                EXIT;  -- 讀完結束
        END;

        -- 解析 CSV: name,addr_jd,region
        v_pos1 := INSTR(v_line, ',', 1, 1);
        v_pos2 := INSTR(v_line, ',', 1, 2);

        v_name   := SUBSTR(v_line, 1, v_pos1 - 1);
        v_addr   := SUBSTR(v_line, v_pos1 + 1, v_pos2 - v_pos1 - 1);
        v_region := SUBSTR(v_line, v_pos2 + 1);

        INSERT INTO test_loadfile (name, addr_jd, region)
        VALUES (v_name, v_addr, v_region);
    END LOOP;

    UTL_FILE.FCLOSE(v_file);
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        IF UTL_FILE.IS_OPEN(v_file) THEN
            UTL_FILE.FCLOSE(v_file);
        END IF;
        RAISE;
END loadfiledata;
/
SHOW ERRORS

測試呼叫

DECLARE
    v_path     VARCHAR2(200) := 'F:\data\';
    v_filename VARCHAR2(200) := '地址資訊.txt';
BEGIN
    loadfiledata(v_path, v_filename);
END;
/
COMMIT;

檔案格式範例 (地址資訊.txt)

台北分公司,台北市中正區重慶南路一段,TP
高雄營運中心,高雄市前金區成功一路,KH
台中服務處,台中市西屯區台灣大道三段,TX

常見錯誤與解決

錯誤	原因	解決
ORA-29280: invalid directory path	UTL_FILE_DIR 未設定或路徑不符	檢查參數、重啟實例、確認 OS 權限
ORA-29283: invalid file operation	檔案不存在、權限不足、磁碟滿	確認檔案存在、Oracle 服務帳號讀寫權限
ORA-06502: numeric or value error	行緩衝區太小 (預設 1024)	FOPEN 第 4 參數指定較大值 (如 32767)