【PL/SQL】外部ファイルを読み書きする方法（UTL_FILEの基本と注意点）

データベースからサーバ上のファイルを直接読み書きしたい場面では、PL/SQL標準パッケージのUTL_FILEが有効です。ログ出力やCSVの生成、取り込み前の軽い整形などをデータベース内だけで完結でき、バッチ処理の自動化にも向いています。ここではディレクトリオブジェクトの準備から、テキストファイルの読み込みと書き込み、追記、例外とクリーンアップ、文字コードや最大行長の扱い、運用上の注意点までを実務視点で解説します。

前提準備：ディレクトリオブジェクトと権限

UTL_FILEはOSパスへ直接アクセスせず「ディレクトリオブジェクト」を経由します。管理者がファイル配置先ディレクトリをDBに登録し、利用スキーマへREAD/WRITEを付与します。OS側ではOracleサービス実行ユーザーに対して対象ディレクトリの読み書き権が必要です。

-- DBA（または相応の権限）で一度だけ実行
CREATE OR REPLACE DIRECTORY APP_DIR AS '/u01/app/appfiles';
GRANT READ, WRITE ON DIRECTORY APP_DIR TO APP_USER;

-- 以降、APP_USER からは UTL_FILE.FOPEN('APP_DIR', 'foo.txt', 'w') のように利用

旧来の初期化パラメータUTL_FILE_DIRは非推奨です。必ずCREATE DIRECTORYを使うと、到達範囲の管理と監査が容易になります。

基本の書き込み：新規作成と追記、改行と最大行長

ファイルはFOPENで開き、PUTまたはPUT_LINEで書き、NEW_LINEで任意に改行し、最後にFCLOSEで閉じます。open_modeは’w’で新規作成（既存は上書き）、’a’で追記、’r’で読み込みです。max_linesizeは1〜32767まで指定でき、行の書き出しや読み込みの上限に影響します。

CREATE OR REPLACE PROCEDURE write_daily_report(p_run_id NUMBER) IS
  f UTL_FILE.file_type;
BEGIN
  f := UTL_FILE.FOPEN(location => 'APP_DIR',
                      filename => 'daily_report.csv',
                      open_mode => 'w',               -- 新規作成
                      max_linesize => 32767);         -- 行の最大長（推奨は十分大きく）
  UTL_FILE.PUT_LINE(f, 'run_id,finished_at,status');
  UTL_FILE.PUT_LINE(f, p_run_id||','||TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD"T"HH24:MI:SS.FF3')||',OK');
  UTL_FILE.FCLOSE(f);
EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.IS_OPEN(f) THEN UTL_FILE.FCLOSE(f); END IF;
    RAISE;
END;
/

既存ファイルへ追記する場合はopen_modeを’a’にします。長い1行を出力する時にmax_linesizeを超えるとWRITE_ERRORになるため、適宜区切って複数回PUTするか、CSVの列構成を見直します。

基本の読み込み：EOF検出と安全なクローズ

GET_LINEは1行読み出しAPIで、ファイル末尾を超えるとNO_DATA_FOUNDが上がります。この例外を契機にループを抜けるのが典型です。読み込み後は必ずFCLOSEで明示的にクローズします。

CREATE OR REPLACE PROCEDURE load_staging_from_csv IS
  f   UTL_FILE.file_type;
  vln VARCHAR2(32767);
BEGIN
  f := UTL_FILE.FOPEN('APP_DIR', 'import.csv', 'r', 32767);
  LOOP
    BEGIN
      UTL_FILE.GET_LINE(f, vln);
      -- 必要ならヘッダ除外やTRIM、引用符の除去をここで実施
      INSERT INTO staging_raw(line_text, loaded_at) VALUES (vln, SYSTIMESTAMP);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        EXIT; -- EOF
    END;
  END LOOP;
  UTL_FILE.FCLOSE(f);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.IS_OPEN(f) THEN UTL_FILE.FCLOSE(f); END IF;
    RAISE;
END;
/

CSVの本格パースが必要なら外部表やSQL*Loaderも検討してください。UTL_FILEはシンプルな行志向処理に向きます。

追記とローテーション：日付入りファイル名の動的生成

運用では日毎ファイルを分けると保守が容易です。ファイル名に日付を含めて追記運用するテンプレートを示します。

CREATE OR REPLACE PROCEDURE append_log(p_msg VARCHAR2) IS
  f   UTL_FILE.file_type;
  fn  VARCHAR2(64) := 'app_'||TO_CHAR(SYSDATE,'YYYYMMDD')||'.log';
BEGIN
  f := UTL_FILE.FOPEN('APP_DIR', fn, 'a', 32767);
  UTL_FILE.PUT_LINE(f, TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF3')||' '||p_msg);
  UTL_FILE.FCLOSE(f);
EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.IS_OPEN(f) THEN UTL_FILE.FCLOSE(f); END IF;
    RAISE;
END;
/

アーカイブや削除のポリシーはOS側ジョブやDBMS_SCHEDULERと組み合わせて計画的に行います。

文字コードの勘所：データベース文字セットとNCHAR版API

UTL_FILEは基本的にデータベース文字セットで入出力します。UTF-8データベースなら日本語も問題なく扱えますが、サーバ側の後工程が別エンコーディングを要求する場合は注意が必要です。NCHARデータを扱うFOPEN_NCHAR/PUT_NCHAR/GET_NCHARも提供されていますが、異なるエンコーディング間の自動変換器ではありません。文字化けが起きる場合はDBキャラクタセットの見直し、外部プロセスによる再エンコード、もしくはBLOBで書き出して外部で変換する設計が安全です。

例外ハンドリング：代表的なエラーと対処

INVALID_PATHはディレクトリ名が見つからない、またはCREATE DIRECTORY未実施のときに発生します。INVALID_MODEはopen_modeが不正な場合、INVALID_OPERATIONは読み取り専用で書いたなど操作不整合のケースで上がります。WRITE_ERRORやREAD_ERRORはOS側権限やディスクフル、行長超過などが原因です。すべてをWHEN OTHERSで握らず、ログテーブルにORAエラーとファイル名、行番号、処理IDを残しておくと運用が安定します。

EXCEPTION
  WHEN UTL_FILE.INVALID_PATH THEN
    INSERT INTO file_err(log_time, code, msg) VALUES (SYSTIMESTAMP, SQLCODE, SQLERRM);
    RAISE;
  WHEN UTL_FILE.WRITE_ERROR OR UTL_FILE.READ_ERROR THEN
    INSERT INTO file_err(log_time, code, msg) VALUES (SYSTIMESTAMP, SQLCODE, SQLERRM);
    RAISE;
  WHEN OTHERS THEN
    INSERT INTO file_err(log_time, code, msg) VALUES (SYSTIMESTAMP, SQLCODE, SQLERRM);
    RAISE;

安全対策：パス固定・ファイル名ホワイトリスト・サイズ管理

ファイルアクセスは必ず固定のディレクトリオブジェクトを経由し、アプリから任意のOSパスを受け取らない設計にします。ユーザー入力でファイル名を組み立てる場合は英数字と限定した正規表現で検証し、相対パスや「..」を拒否すると安全です。出力サイズが肥大化しやすいログは日次ローテーションと圧縮前提にし、ディスク容量監視を合わせて実施します。OS側の所有権とパーミッションも定期点検して、意図しない読み書き経路がないかを確認します。

読み書きの実務パターン：外部表・パイプラインとの使い分け

大量CSVの取り込みは外部表を使うと並列化やスキップ、型変換の表現力が高く、UTL_FILEは前段の整形や小規模ファイルに向きます。書き出しはUTL_FILEでCSVを作る以外に、外部表+INSERT…SELECTでOSファイルへ出力する手もあります。リアルタイム連携が必要ならDBMS_PIPEやAQで別プロセスへ渡し、ファイル書き出しはアプリ側に任せる選択も現実的です。

まとめ

UTL_FILEは「サーバ上の既定ディレクトリに対する行志向の入出力」をシンプルに提供します。CREATE DIRECTORYとREAD/WRITE権限を正しく整え、FOPEN→処理→FCLOSEの型を例外時も徹底し、最大行長と文字コードの制約を理解しておけば、レポート生成や軽量な取り込み、運用ログの記録といった用途で堅実に機能します。大容量や厳密なCSVパースが必要なケースは外部表などと使い分け、ファイル名検証とディスク運用の基本を押さえることで、安全でトラブルの少ないファイル連携をPL/SQLだけで実現できます。