【Oracle】UTL_FILE完全ガイド｜PL/SQLでファイルを読み書きする方法・DIRECTORY設定・CSV出力・エラー対処まで実例で解説

-- DIRECTORY オブジェクトの作成（DBA 権限が必要）
-- OS 上の実際のディレクトリが存在している必要がある
CREATE OR REPLACE DIRECTORY data_dir AS '/home/oracle/data';
CREATE OR REPLACE DIRECTORY log_dir  AS '/home/oracle/logs';

-- 作成済み DIRECTORY の確認
SELECT directory_name, directory_path
FROM dba_directories
ORDER BY directory_name;

-- ユーザーに READ / WRITE 権限を付与
GRANT READ, WRITE ON DIRECTORY data_dir TO app_user;
GRANT READ        ON DIRECTORY data_dir TO read_only_user;

-- 現在ユーザーが持つ DIRECTORY 権限を確認
SELECT grantee, grantor, table_name AS directory_name, privilege
FROM dba_tab_privs
WHERE table_name IN (SELECT directory_name FROM dba_directories)
ORDER BY grantee, directory_name;

DECLARE
    v_fh  UTL_FILE.FILE_TYPE;   -- ファイルハンドル
BEGIN
    -- FOPEN(ディレクトリ名, ファイル名, モード, 最大行サイズ)
    -- モード 'w' = 上書き新規作成（既存ファイルは上書き）
    -- max_linesize のデフォルトは 1024、最大 32767
    v_fh := UTL_FILE.FOPEN('LOG_DIR', 'process.log', 'w', 32767);

    UTL_FILE.PUT_LINE(v_fh, '処理開始: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
    UTL_FILE.PUT_LINE(v_fh, '対象件数: 1000');
    UTL_FILE.PUT_LINE(v_fh, '処理完了');

    UTL_FILE.FCLOSE(v_fh);

EXCEPTION
    WHEN UTL_FILE.INVALID_PATH THEN
        DBMS_OUTPUT.PUT_LINE('ディレクトリが存在しないか権限がありません');
    WHEN UTL_FILE.INVALID_OPERATION THEN
        DBMS_OUTPUT.PUT_LINE('ファイルのオープンに失敗しました');
    WHEN OTHERS THEN
        -- 例外発生時もファイルを確実にクローズする
        IF UTL_FILE.IS_OPEN(v_fh) THEN
            UTL_FILE.FCLOSE(v_fh);
        END IF;
        RAISE;
END;
/

CREATE OR REPLACE PROCEDURE export_employees_csv (
    p_directory IN VARCHAR2 DEFAULT 'DATA_DIR',
    p_filename  IN VARCHAR2 DEFAULT 'employees.csv'
) AS
    v_fh     UTL_FILE.FILE_TYPE;
    v_line   VARCHAR2(32767);

    -- CSV の値にカンマ・ダブルクォートが含まれる場合に安全に囲む関数
    FUNCTION csv_val(p_val IN VARCHAR2) RETURN VARCHAR2 IS
    BEGIN
        IF p_val IS NULL THEN
            RETURN '';
        ELSIF INSTR(p_val, ',') > 0 OR INSTR(p_val, '"') > 0 OR INSTR(p_val, CHR(10)) > 0 THEN
            -- カンマ・ダブルクォート・改行を含む場合はダブルクォートで囲み、
            -- 内部のダブルクォートは2つに重ねてエスケープ
            RETURN '"' || REPLACE(p_val, '"', '""') || '"';
        ELSE
            RETURN p_val;
        END IF;
    END csv_val;

BEGIN
    v_fh := UTL_FILE.FOPEN(p_directory, p_filename, 'w', 32767);

    -- ヘッダー行
    UTL_FILE.PUT_LINE(v_fh, 'employee_id,last_name,first_name,salary,department_id,hire_date');

    -- データ行をカーソル FOR LOOP で出力
    FOR r IN (
        SELECT employee_id, last_name, first_name,
               salary, department_id,
               TO_CHAR(hire_date, 'YYYY-MM-DD') AS hire_date
        FROM employees
        ORDER BY employee_id
    ) LOOP
        v_line :=        r.employee_id          || ','
               || csv_val(r.last_name)          || ','
               || csv_val(r.first_name)         || ','
               || NVL(TO_CHAR(r.salary),  '')   || ','
               || NVL(TO_CHAR(r.department_id),'') || ','
               || r.hire_date;
        UTL_FILE.PUT_LINE(v_fh, v_line);
    END LOOP;

    UTL_FILE.FCLOSE(v_fh);
    DBMS_OUTPUT.PUT_LINE(p_filename || ' の出力が完了しました');

EXCEPTION
    WHEN OTHERS THEN
        IF UTL_FILE.IS_OPEN(v_fh) THEN
            UTL_FILE.FCLOSE(v_fh);
        END IF;
        RAISE;
END export_employees_csv;
/

-- 実行例
BEGIN
    export_employees_csv('DATA_DIR', 'emp_20250406.csv');
END;
/

DECLARE
    v_fh     UTL_FILE.FILE_TYPE;
    v_line   VARCHAR2(32767);
    v_lineno NUMBER := 0;
BEGIN
    -- モード 'r' = 読み込み専用
    v_fh := UTL_FILE.FOPEN('DATA_DIR', 'input.txt', 'r', 32767);

    LOOP
        BEGIN
            UTL_FILE.GET_LINE(v_fh, v_line);  -- 1行読み込む
            v_lineno := v_lineno + 1;
            DBMS_OUTPUT.PUT_LINE(v_lineno || ': ' || v_line);
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                EXIT;  -- EOF に達したらループを抜ける
        END;
    END LOOP;

    UTL_FILE.FCLOSE(v_fh);
    DBMS_OUTPUT.PUT_LINE('読み込み完了: ' || v_lineno || '行');

EXCEPTION
    WHEN UTL_FILE.INVALID_PATH THEN
        DBMS_OUTPUT.PUT_LINE('ディレクトリまたはファイルが見つかりません');
    WHEN OTHERS THEN
        IF UTL_FILE.IS_OPEN(v_fh) THEN
            UTL_FILE.FCLOSE(v_fh);
        END IF;
        RAISE;
END;
/

CREATE OR REPLACE PROCEDURE import_csv (
    p_directory IN VARCHAR2,
    p_filename  IN VARCHAR2
) AS
    v_fh      UTL_FILE.FILE_TYPE;
    v_line    VARCHAR2(32767);
    v_lineno  PLS_INTEGER := 0;
    v_loaded  PLS_INTEGER := 0;

    -- カンマ区切りの n 番目フィールドを取得するヘルパー
    FUNCTION get_field(p_line VARCHAR2, p_pos PLS_INTEGER) RETURN VARCHAR2 IS
        v_start PLS_INTEGER;
        v_end   PLS_INTEGER;
    BEGIN
        v_start := CASE p_pos WHEN 1 THEN 1
                   ELSE INSTR(p_line, ',', 1, p_pos - 1) + 1 END;
        v_end   := INSTR(p_line, ',', 1, p_pos);
        IF v_end = 0 THEN
            RETURN SUBSTR(p_line, v_start);      -- 最後のフィールド
        ELSE
            RETURN SUBSTR(p_line, v_start, v_end - v_start);
        END IF;
    END get_field;

BEGIN
    v_fh := UTL_FILE.FOPEN(p_directory, p_filename, 'r', 32767);

    LOOP
        BEGIN
            UTL_FILE.GET_LINE(v_fh, v_line);
            v_lineno := v_lineno + 1;

            -- 1行目（ヘッダー）はスキップ
            IF v_lineno = 1 THEN CONTINUE; END IF;
            -- 空行はスキップ
            IF TRIM(v_line) IS NULL THEN CONTINUE; END IF;

            INSERT INTO emp_staging (emp_id, last_name, salary, hire_date)
            VALUES (
                TO_NUMBER(get_field(v_line, 1)),
                get_field(v_line, 2),
                TO_NUMBER(get_field(v_line, 3)),
                TO_DATE(get_field(v_line, 4), 'YYYY-MM-DD')
            );
            v_loaded := v_loaded + 1;

        EXCEPTION
            WHEN NO_DATA_FOUND THEN EXIT;    -- EOF
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('行' || v_lineno || 'エラー: ' || SQLERRM);
                -- エラー行をスキップして続行する場合はここで CONTINUE
        END;
    END LOOP;

    COMMIT;
    UTL_FILE.FCLOSE(v_fh);
    DBMS_OUTPUT.PUT_LINE(v_loaded || '件 INSERT しました');

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        IF UTL_FILE.IS_OPEN(v_fh) THEN UTL_FILE.FCLOSE(v_fh); END IF;
        RAISE;
END import_csv;
/

DECLARE
    v_exists     BOOLEAN;
    v_file_len   NUMBER;
    v_block_size BINARY_INTEGER;
BEGIN
    -- ファイルの存在確認とサイズ取得（FGETATTR）
    UTL_FILE.FGETATTR(
        location   => 'DATA_DIR',
        filename   => 'output.csv',
        fexists    => v_exists,
        file_length => v_file_len,
        block_size  => v_block_size
    );

    IF v_exists THEN
        DBMS_OUTPUT.PUT_LINE('ファイルサイズ: ' || v_file_len || ' バイト');

        -- 古いファイルをバックアップ用にリネームしてから新規作成
        UTL_FILE.FRENAME(
            src_location  => 'DATA_DIR',
            src_filename  => 'output.csv',
            dest_location => 'DATA_DIR',
            dest_filename => 'output_' || TO_CHAR(SYSDATE, 'YYYYMMDD') || '.bak',
            overwrite     => FALSE   -- TRUE にすると同名ファイルを上書き
        );
        DBMS_OUTPUT.PUT_LINE('バックアップ完了');
    ELSE
        DBMS_OUTPUT.PUT_LINE('ファイルが存在しません');
    END IF;

    -- ファイルの削除（存在しない場合は UTL_FILE.INVALID_OPERATION が発生）
    -- UTL_FILE.FREMOVE('DATA_DIR', 'old_file.txt');
END;
/