【PL/SQL】例外をJSON形式で返すREST対応API設計

{
  "ok": false,
  "code": "DUPLICATE",
  "message": "duplicate request id",
  "retryable": false,
  "correlationId": "7F4A9C1E...",
  "details": {"key":"REQ-12345"}
}

CREATE OR REPLACE PACKAGE api_error AS
  FUNCTION corr_id RETURN VARCHAR2;

  FUNCTION json_fail(
    p_code      VARCHAR2,
    p_msg       VARCHAR2,
    p_retryable BOOLEAN,
    p_corr      VARCHAR2,
    p_details   CLOB DEFAULT NULL
  ) RETURN CLOB;

  FUNCTION json_ok(
    p_payload CLOB,
    p_corr    VARCHAR2
  ) RETURN CLOB;
END;
/

CREATE OR REPLACE PACKAGE BODY api_error AS
  FUNCTION corr_id RETURN VARCHAR2 IS
  BEGIN
    RETURN RAWTOHEX(SYS_GUID());
  END;

  FUNCTION json_fail(
    p_code      VARCHAR2,
    p_msg       VARCHAR2,
    p_retryable BOOLEAN,
    p_corr      VARCHAR2,
    p_details   CLOB
  ) RETURN CLOB IS
    v_json       CLOB;
    v_retry_json VARCHAR2(5);
  BEGIN
    IF p_retryable THEN
      v_retry_json := 'true';
    ELSE
      v_retry_json := 'false';
    END IF;

    SELECT JSON_OBJECT(
             'ok'            VALUE 'false' FORMAT JSON,
             'code'          VALUE p_code,
             'message'       VALUE p_msg,
             'retryable'     VALUE v_retry_json FORMAT JSON,
             'correlationId' VALUE p_corr,
             'details'       VALUE COALESCE(p_details, 'null') FORMAT JSON
           RETURNING CLOB)
      INTO v_json
      FROM dual;

    RETURN v_json;
  END;

  FUNCTION json_ok(
    p_payload CLOB,
    p_corr    VARCHAR2
  ) RETURN CLOB IS
    v_json CLOB;
  BEGIN
    SELECT JSON_OBJECT(
             'ok'            VALUE 'true' FORMAT JSON,
             'correlationId' VALUE p_corr,
             'data'          VALUE COALESCE(p_payload, 'null') FORMAT JSON
           RETURNING CLOB)
      INTO v_json
      FROM dual;

    RETURN v_json;
  END;
END;
/

CREATE OR REPLACE PACKAGE err_map IS
  deadlock_ex    EXCEPTION; PRAGMA EXCEPTION_INIT(deadlock_ex,    -60);   -- ORA-00060
  lock_nowait_ex EXCEPTION; PRAGMA EXCEPTION_INIT(lock_nowait_ex, -54);   -- ORA-00054
  seri_conflict  EXCEPTION; PRAGMA EXCEPTION_INIT(seri_conflict,  -8177); -- ORA-08177
  unique_violate EXCEPTION; PRAGMA EXCEPTION_INIT(unique_violate, -1);    -- ORA-00001
  no_priv        EXCEPTION; PRAGMA EXCEPTION_INIT(no_priv,        -1031); -- ORA-01031
END;
/

CREATE OR REPLACE PACKAGE order_domain AS
  PROCEDURE upsert_order(p_req_id VARCHAR2, p_cust NUMBER, p_amt NUMBER);
  FUNCTION  view_order(p_req_id VARCHAR2) RETURN CLOB;
END;
/

CREATE OR REPLACE PACKAGE BODY order_domain AS
  PROCEDURE upsert_order(p_req_id VARCHAR2, p_cust NUMBER, p_amt NUMBER) IS
  BEGIN
    MERGE INTO orders d
    USING (SELECT p_req_id req_id, p_cust cust, p_amt amt FROM dual) s
       ON (d.req_id = s.req_id)
     WHEN MATCHED THEN
       UPDATE SET d.cust_id = s.cust,
                  d.amount  = s.amt,
                  d.updated_at = SYSTIMESTAMP
     WHEN NOT MATCHED THEN
       INSERT (req_id, cust_id, amount, created_at)
       VALUES (s.req_id, s.cust, s.amt, SYSTIMESTAMP);
  END;

  FUNCTION view_order(p_req_id VARCHAR2) RETURN CLOB IS
    v_json CLOB;
  BEGIN
    SELECT JSON_OBJECT(
             'reqId'  VALUE req_id,
             'custId' VALUE cust_id,
             'amount' VALUE amount
           RETURNING CLOB)
      INTO v_json
      FROM orders
     WHERE req_id = p_req_id;

    RETURN v_json;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20010, 'order not found');
  END;
END;
/

CREATE OR REPLACE FUNCTION api_upsert_order(
  p_req_id VARCHAR2,
  p_cust   NUMBER,
  p_amt    NUMBER
) RETURN CLOB
IS
  v_corr VARCHAR2(64) := api_error.corr_id();
BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE('ORDERS_API', 'UPSERT');
  DBMS_SESSION.SET_IDENTIFIER(v_corr);

  order_domain.upsert_order(p_req_id, p_cust, p_amt);

  RETURN api_error.json_ok(
           p_payload => JSON_OBJECT('reqId' VALUE p_req_id RETURNING CLOB),
           p_corr    => v_corr);
EXCEPTION
  WHEN err_map.lock_nowait_ex OR err_map.deadlock_ex OR err_map.seri_conflict THEN
    RETURN api_error.json_fail(
             p_code      => 'RETRYABLE_CONFLICT',
             p_msg       => 'concurrency conflict',
             p_retryable => TRUE,
             p_corr      => v_corr,
             p_details   => JSON_OBJECT('reqId' VALUE p_req_id RETURNING CLOB));

  WHEN err_map.unique_violate THEN
    RETURN api_error.json_fail(
             p_code      => 'DUPLICATE',
             p_msg       => 'duplicate request id',
             p_retryable => FALSE,
             p_corr      => v_corr,
             p_details   => JSON_OBJECT('reqId' VALUE p_req_id RETURNING CLOB));

  WHEN err_map.no_priv THEN
    RETURN api_error.json_fail('FORBIDDEN', 'insufficient privileges', FALSE, v_corr, NULL);

  WHEN OTHERS THEN
    RETURN api_error.json_fail('UNEXPECTED', SQLERRM, FALSE, v_corr, NULL);
END;
/

BEGIN
  :body := api_upsert_order(
             p_req_id => :req_id,
             p_cust   => :cust_id,
             p_amt    => :amount);

  -- ORDS側では application/json として返す設計にする
  -- HTTPステータスはハンドラ設計に応じて別途制御する
END;

CREATE OR REPLACE PROCEDURE api_audit(
  p_corr VARCHAR2,
  p_code VARCHAR2,
  p_msg  VARCHAR2
) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO api_log(correlation_id, code, message, at)
  VALUES (p_corr, p_code, SUBSTR(p_msg, 1, 4000), SYSTIMESTAMP);
  COMMIT;
END;
/