【PL/SQL】UTL_FILEでCSV・ログファイルを読み書きする方法｜DIRECTORY設定とエラー対処

-- DBAまたは権限を持つユーザーで実行
CREATE OR REPLACE DIRECTORY APP_EXPORT_DIR AS '/u01/app/export';

GRANT READ, WRITE ON DIRECTORY APP_EXPORT_DIR TO APP_USER;

-- 必要に応じて確認
SELECT directory_name, directory_path
FROM all_directories
WHERE directory_name = 'APP_EXPORT_DIR';

DECLARE
  l_file UTL_FILE.FILE_TYPE;
BEGIN
  l_file := UTL_FILE.FOPEN(
              location     => 'APP_EXPORT_DIR',
              filename     => 'orders.csv',
              open_mode    => 'w',
              max_linesize => 32767
            );

  UTL_FILE.PUT_LINE(l_file, 'ORDER_ID,CUSTOMER_ID,AMOUNT');

  FOR r IN (
    SELECT order_id, customer_id, amount
    FROM orders
    ORDER BY order_id
  ) LOOP
    UTL_FILE.PUT_LINE(
      l_file,
      r.order_id || ',' ||
      r.customer_id || ',' ||
      r.amount
    );
  END LOOP;

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

CREATE OR REPLACE FUNCTION csv_value(p_value IN VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
  IF p_value IS NULL THEN
    RETURN '""';
  END IF;

  RETURN '"' || REPLACE(p_value, '"', '""') || '"';
END;
/

-- 使用例
SELECT csv_value('A,B "test"') AS escaped_value
FROM dual;

DECLARE
  l_file UTL_FILE.FILE_TYPE;
  l_line VARCHAR2(32767);
BEGIN
  l_file := UTL_FILE.FOPEN(
              location     => 'APP_EXPORT_DIR',
              filename     => 'orders.csv',
              open_mode    => 'r',
              max_linesize => 32767
            );

  LOOP
    BEGIN
      UTL_FILE.GET_LINE(l_file, l_line);

      -- ここでCSVを解析してテーブルへ取り込む
      DBMS_OUTPUT.PUT_LINE(l_line);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        EXIT;
    END;
  END LOOP;

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

CREATE OR REPLACE PROCEDURE write_batch_log(
  p_message IN VARCHAR2
) AS
  l_file UTL_FILE.FILE_TYPE;
BEGIN
  l_file := UTL_FILE.FOPEN(
              location     => 'APP_EXPORT_DIR',
              filename     => 'batch.log',
              open_mode    => 'a',
              max_linesize => 32767
            );

  UTL_FILE.PUT_LINE(
    l_file,
    TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF3') ||
    ' ' || p_message
  );

  UTL_FILE.FFLUSH(l_file);
  UTL_FILE.FCLOSE(l_file);
EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.IS_OPEN(l_file) THEN
      UTL_FILE.FCLOSE(l_file);
    END IF;
    RAISE;
END;
/

CREATE OR REPLACE FUNCTION safe_export_filename(
  p_filename IN VARCHAR2
) RETURN VARCHAR2
AS
BEGIN
  IF p_filename IS NULL THEN
    RAISE_APPLICATION_ERROR(-20001, 'filename is required');
  END IF;

  IF NOT REGEXP_LIKE(p_filename, '^[A-Za-z0-9_-]+\.csv$') THEN
    RAISE_APPLICATION_ERROR(-20002, 'invalid filename');
  END IF;

  RETURN p_filename;
END;
/

DECLARE
  l_file UTL_FILE.FILE_TYPE;
BEGIN
  l_file := UTL_FILE.FOPEN_NCHAR(
              location     => 'APP_EXPORT_DIR',
              filename     => 'message.txt',
              open_mode    => 'w',
              max_linesize => 32767
            );

  UTL_FILE.PUT_LINE_NCHAR(l_file, N'日本語メッセージ');
  UTL_FILE.FCLOSE(l_file);
END;
/

DECLARE
  l_in_file  UTL_FILE.FILE_TYPE;
  l_out_file UTL_FILE.FILE_TYPE;
BEGIN
  l_in_file := UTL_FILE.FOPEN('APP_EXPORT_DIR', 'input.csv', 'r', 32767);
  l_out_file := UTL_FILE.FOPEN('APP_EXPORT_DIR', 'output.csv', 'w', 32767);

  -- file processing
  NULL;

  UTL_FILE.FCLOSE(l_in_file);
  UTL_FILE.FCLOSE(l_out_file);
EXCEPTION
  WHEN OTHERS THEN
    UTL_FILE.FCLOSE_ALL;
    RAISE;
END;
/

CREATE OR REPLACE PROCEDURE export_orders_daily AS
  l_filename VARCHAR2(128);
BEGIN
  l_filename := 'orders_' || TO_CHAR(SYSDATE, 'YYYYMMDD') || '.csv';
  l_filename := safe_export_filename(l_filename);

  -- 実際のCSV出力処理を呼び出す
  -- write_orders_csv(l_filename);

  INSERT INTO batch_log(process_name, status, message, created_at)
  VALUES ('export_orders_daily', 'SUCCESS', l_filename, SYSDATE);
EXCEPTION
  WHEN OTHERS THEN
    INSERT INTO batch_log(process_name, status, message, created_at)
    VALUES ('export_orders_daily', 'ERROR', SQLERRM, SYSDATE);
    RAISE;
END;
/