【PL/SQL】業務エラーコード設計｜RAISE_APPLICATION_ERROR・メッセージテーブル・ログ連携まで

CREATE TABLE app_error_message (
  error_code        VARCHAR2(50)  NOT NULL,
  locale           VARCHAR2(10)  DEFAULT 'ja' NOT NULL,
  oracle_error_no   NUMBER       NOT NULL,
  severity          VARCHAR2(20) DEFAULT 'ERROR' NOT NULL,
  user_message      VARCHAR2(1000) NOT NULL,
  log_message       VARCHAR2(2000),
  http_status       NUMBER DEFAULT 400 NOT NULL,
  retryable         CHAR(1) DEFAULT 'N' NOT NULL,
  enabled           CHAR(1) DEFAULT 'Y' NOT NULL,
  updated_by        VARCHAR2(128) DEFAULT SYS_CONTEXT('USERENV','SESSION_USER') NOT NULL,
  updated_at        TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
  CONSTRAINT pk_app_error_message PRIMARY KEY (error_code, locale),
  CONSTRAINT ck_app_error_no CHECK (oracle_error_no BETWEEN -20999 AND -20000),
  CONSTRAINT ck_app_error_severity CHECK (severity IN ('INFO','WARN','ERROR','FATAL')),
  CONSTRAINT ck_app_error_retry CHECK (retryable IN ('Y','N')),
  CONSTRAINT ck_app_error_enabled CHECK (enabled IN ('Y','N'))
);

CREATE UNIQUE INDEX uq_app_error_message_no
  ON app_error_message(oracle_error_no, locale);

INSERT INTO app_error_message (
  error_code, locale, oracle_error_no, severity,
  user_message, log_message, http_status, retryable
) VALUES (
  'ORDER_CLOSED_PERIOD', 'ja', -20101, 'ERROR',
  '締め済み期間のため登録できません。',
  'order_date={1}, closing_period={2}',
  409, 'N'
);

INSERT INTO app_error_message (
  error_code, locale, oracle_error_no, severity,
  user_message, log_message, http_status, retryable
) VALUES (
  'ORDER_STOCK_SHORTAGE', 'ja', -20102, 'ERROR',
  '在庫が不足しています。',
  'item_code={1}, requested_qty={2}, available_qty={3}',
  409, 'N'
);

INSERT INTO app_error_message (
  error_code, locale, oracle_error_no, severity,
  user_message, log_message, http_status, retryable
) VALUES (
  'EXTERNAL_TIMEOUT', 'ja', -20910, 'WARN',
  '外部システムとの通信に失敗しました。時間をおいて再実行してください。',
  'destination={1}, timeout_seconds={2}',
  503, 'Y'
);

CREATE OR REPLACE PACKAGE app_error AS
  PROCEDURE raise_error(
    p_error_code VARCHAR2,
    p_arg1       VARCHAR2 DEFAULT NULL,
    p_arg2       VARCHAR2 DEFAULT NULL,
    p_arg3       VARCHAR2 DEFAULT NULL,
    p_locale     VARCHAR2 DEFAULT 'ja'
  );

  FUNCTION message_of(
    p_error_code VARCHAR2,
    p_locale     VARCHAR2 DEFAULT 'ja'
  ) RETURN VARCHAR2;

  FUNCTION current_code RETURN VARCHAR2;
END;
/

CREATE OR REPLACE PACKAGE BODY app_error AS
  g_current_code VARCHAR2(50);

  FUNCTION replace_args(
    p_template VARCHAR2,
    p_arg1     VARCHAR2,
    p_arg2     VARCHAR2,
    p_arg3     VARCHAR2
  ) RETURN VARCHAR2 IS
  BEGIN
    RETURN REPLACE(
             REPLACE(
               REPLACE(p_template, '{1}', NVL(p_arg1, '')),
             '{2}', NVL(p_arg2, '')),
           '{3}', NVL(p_arg3, ''));
  END;

  FUNCTION message_of(
    p_error_code VARCHAR2,
    p_locale     VARCHAR2
  ) RETURN VARCHAR2 IS
    v_message app_error_message.user_message%TYPE;
  BEGIN
    SELECT user_message
      INTO v_message
      FROM app_error_message
     WHERE error_code = p_error_code
       AND locale = p_locale
       AND enabled = 'Y';

    RETURN v_message;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RETURN '処理を完了できませんでした。';
  END;

  PROCEDURE raise_error(
    p_error_code VARCHAR2,
    p_arg1       VARCHAR2,
    p_arg2       VARCHAR2,
    p_arg3       VARCHAR2,
    p_locale     VARCHAR2
  ) IS
    v_no      app_error_message.oracle_error_no%TYPE;
    v_message app_error_message.user_message%TYPE;
  BEGIN
    g_current_code := p_error_code;

    SELECT oracle_error_no, user_message
      INTO v_no, v_message
      FROM app_error_message
     WHERE error_code = p_error_code
       AND locale = p_locale
       AND enabled = 'Y';

    RAISE_APPLICATION_ERROR(
      v_no,
      p_error_code || ': ' || replace_args(v_message, p_arg1, p_arg2, p_arg3),
      TRUE
    );
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      g_current_code := 'UNKNOWN_ERROR_CODE';
      RAISE_APPLICATION_ERROR(-20999, 'UNKNOWN_ERROR_CODE: ' || p_error_code, TRUE);
  END;

  FUNCTION current_code RETURN VARCHAR2 IS
  BEGIN
    RETURN g_current_code;
  END;
END;
/

CREATE OR REPLACE PROCEDURE register_order(
  p_order_date DATE,
  p_item_code  VARCHAR2,
  p_qty        NUMBER
) AS
  v_closing_period DATE;
  v_available_qty  NUMBER;
BEGIN
  v_closing_period := DATE '2026-05-31';

  IF p_order_date <= v_closing_period THEN
    app_error.raise_error(
      p_error_code => 'ORDER_CLOSED_PERIOD',
      p_arg1       => TO_CHAR(p_order_date, 'YYYY-MM-DD'),
      p_arg2       => TO_CHAR(v_closing_period, 'YYYY-MM-DD')
    );
  END IF;

  SELECT stock_qty
    INTO v_available_qty
    FROM item_stock
   WHERE item_code = p_item_code;

  IF v_available_qty < p_qty THEN
    app_error.raise_error(
      p_error_code => 'ORDER_STOCK_SHORTAGE',
      p_arg1       => p_item_code,
      p_arg2       => TO_CHAR(p_qty),
      p_arg3       => TO_CHAR(v_available_qty)
    );
  END IF;

  -- 注文登録処理
END;
/

CREATE TABLE app_error_log (
  log_id          NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  error_code      VARCHAR2(50),
  oracle_error_no NUMBER,
  module_name     VARCHAR2(128),
  action_name     VARCHAR2(128),
  user_message    VARCHAR2(1000),
  log_detail      VARCHAR2(4000),
  backtrace       CLOB,
  created_by      VARCHAR2(128) DEFAULT SYS_CONTEXT('USERENV','SESSION_USER') NOT NULL,
  created_at      TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
);

CREATE INDEX ix_app_error_log_created
  ON app_error_log(created_at, error_code);

CREATE OR REPLACE PROCEDURE write_error_log(
  p_error_code      VARCHAR2,
  p_oracle_error_no NUMBER,
  p_user_message    VARCHAR2,
  p_detail          VARCHAR2,
  p_backtrace       CLOB
) AS
BEGIN
  INSERT INTO app_error_log(
    error_code,
    oracle_error_no,
    module_name,
    action_name,
    user_message,
    log_detail,
    backtrace
  ) VALUES (
    p_error_code,
    p_oracle_error_no,
    SYS_CONTEXT('USERENV','MODULE'),
    SYS_CONTEXT('USERENV','ACTION'),
    p_user_message,
    p_detail,
    p_backtrace
  );
END;
/

CREATE OR REPLACE FUNCTION error_json(
  p_error_code VARCHAR2,
  p_locale     VARCHAR2 DEFAULT 'ja'
) RETURN CLOB IS
  v_msg app_error_message%ROWTYPE;
BEGIN
  SELECT *
    INTO v_msg
    FROM app_error_message
   WHERE error_code = p_error_code
     AND locale = p_locale
     AND enabled = 'Y';

  RETURN JSON_OBJECT(
    'ok' VALUE false,
    'error' VALUE JSON_OBJECT(
      'code' VALUE v_msg.error_code,
      'message' VALUE v_msg.user_message,
      'severity' VALUE v_msg.severity,
      'retryable' VALUE CASE v_msg.retryable WHEN 'Y' THEN true ELSE false END
    )
    RETURNING CLOB
  );
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN JSON_OBJECT(
      'ok' VALUE false,
      'error' VALUE JSON_OBJECT(
        'code' VALUE 'UNKNOWN_ERROR',
        'message' VALUE '処理を完了できませんでした。',
        'retryable' VALUE false
      )
      RETURNING CLOB
    );
END;
/

BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE('ORDER_IMPORT_BATCH', 'load_orders');

  order_import_main.run;

EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE BETWEEN -20999 AND -20000 THEN
      write_error_log(
        p_error_code      => NVL(app_error.current_code, 'BUSINESS_ERROR'),
        p_oracle_error_no => SQLCODE,
        p_user_message    => SQLERRM,
        p_detail          => 'business error in order import',
        p_backtrace       => DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
      );
    ELSE
      write_error_log(
        p_error_code      => 'UNEXPECTED_ERROR',
        p_oracle_error_no => SQLCODE,
        p_user_message    => '処理中に予期しないエラーが発生しました。',
        p_detail          => SQLERRM,
        p_backtrace       => DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
      );
    END IF;

    RAISE;
END;
/

INSERT INTO app_error_message (
  error_code, locale, oracle_error_no, severity,
  user_message, log_message, http_status, retryable
) VALUES (
  'ORDER_STOCK_SHORTAGE', 'en', -20102, 'ERROR',
  'Insufficient stock.',
  'item_code={1}, requested_qty={2}, available_qty={3}',
  409, 'N'
);