REST対応のPL/SQLでは、失敗時に例外をそのままエラーとして投げ返すのではなく、クライアントが機械的に解釈できるJSONの形に正規化して返す設計が重要になります。業務エラーと一時的障害を区別し、再試行可否や相関IDを含む共通スキーマで包むことで、フロントや連携先は分岐を簡潔に記述できます。ここでは例外をJSONへマッピングする基本方針、共通フォーマッタの実装、再試行パターンや監査との連携、HTTPステータスとの対応付けまでを実運用前提で整理します。
エラーの共通スキーマを先に決める
返却するJSONの骨格を固定します。成功時はokにtrue、失敗時はokにfalseを入れ、コード、メッセージ、リトライ可否、相関ID、追加メタ情報を格納できるようにします。HTTP層でのステータスは別として、ボディの構造は常に一定であるほど扱いやすくなります。
{
"ok": false,
"code": "DUPLICATE",
"message": "duplicate request id",
"retryable": false,
"correlationId": "7F4A9C1E...",
"details": {"key":"REQ-12345"}
}
例外コードの正規化とマッピング方針
ORAコードをそのまま露出させず、業務ドメインに沿った短いコードへ写像します。ロック競合やデッドロック、シリアライズ競合のように再実行で解消する見込みがあるケースはRETRYABLE系にまとめ、入力不備や一意制約違反、権限不足は非再試行として扱います。PL/SQL内ではPRAGMA EXCEPTION_INITで主要なORAを名前付き例外にバインドし、ファサード層でドメインコードに変換します。
共通フォーマッタと相関IDの実装
フォーマッタはエラーJSONを組み立てる専用ユニットとして独立させます。相関IDはSYS_GUIDの16進化や外部ヘッダの伝搬を想定し、常にレスポンスに含めます。
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 CLOB;
BEGIN
SELECT JSON_OBJECT(
'ok' VALUE FALSE,
'code' VALUE p_code,
'message' VALUE p_msg,
'retryable' VALUE CASE WHEN p_retryable THEN TRUE ELSE FALSE END,
'correlationId' VALUE p_corr,
'details' VALUE CASE WHEN p_details IS NOT NULL THEN JSON(p_details) ELSE NULL END
RETURNING CLOB)
INTO v FROM dual;
RETURN v;
END;
FUNCTION json_ok(p_payload CLOB, p_corr VARCHAR2) RETURN CLOB IS
v CLOB;
BEGIN
SELECT JSON_OBJECT(
'ok' VALUE TRUE,
'correlationId' VALUE p_corr,
'data' VALUE JSON(p_payload)
RETURNING CLOB)
INTO v FROM dual;
RETURN v;
END;
END;
/
代表的な例外の分類と名前付き例外
PL/SQL内で対象となる例外を明示しておくと、ファサード層での分岐が読みやすくなります。
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;
/
業務処理はドメインパッケージへ、返却はファサードでJSON化
処理本体は成功か例外かだけを意識し、レスポンス形式への変換はAPIファサードに委ねます。成功時のデータは事前にJSON_OBJECT等で成形しておくと統一できます。
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 CLOB;
BEGIN
SELECT JSON_OBJECT('reqId' VALUE req_id, 'custId' VALUE cust_id, 'amount' VALUE amount
RETURNING CLOB)
INTO v
FROM orders
WHERE req_id = p_req_id;
RETURN v;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20010,'order not found');
END;
END;
/
REST用ファサード関数の設計と例外マッピング
相関IDを生成し、DBMS_APPLICATION_INFOでモジュールやアクションを載せ、分類に基づいてJSONを返します。ORDSやEPC経由で公開する場合は、この関数をエンドポイントに割り当てます。
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('RETRYABLE_CONFLICT','concurrency conflict', TRUE, v_corr,
JSON_OBJECT('reqId' VALUE p_req_id RETURNING CLOB));
WHEN err_map.unique_violate THEN
RETURN api_error.json_fail('DUPLICATE','duplicate request id', FALSE, v_corr,
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;
/
HTTPステータスとの対応付け
ORDSのハンドラやモジュール設定では、PL/SQLの戻りJSONとは別にHTTPステータスを制御します。再試行可能な競合は409、重複や入力不正は400または409、権限不足は403、見つからない系は404、想定外は500を割り当てると分岐が単純化します。ステータスはヘッダ、本文は共通スキーマという二層で設計し、クライアントはまずステータスで大別し、ボディのcodeで詳細分岐する流れにします。
再試行と冪等性の取り扱い
再試行可能な例外を返す以上、同一要求の重複実行で破壊的変化が生じないことが前提になります。自然キーやリクエストIDの一意制約を基盤にし、更新はMERGEや条件付きUPDATEで収束させます。アウトボックステーブルに外部通知を記録してから配送する構成にしておけば、API再試行と外部副作用の二重化を切り離したまま運用できます。
監査・トレースとの連携
DBMS_SESSION.SET_IDENTIFIERに相関IDを入れておくと、V$SESSIONや監査テーブルでリクエスト単位の追跡がしやすくなります。自治トランザクションの軽量ロガーを用意して、失敗時に相関ID、コード、入力キー、経過時間を確定させると、障害対応の初動が劇的に速くなります。
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;
/
ペイロードサイズと型の選択
JSONの生成や返却はCLOBを基本に据えると安全です。VARCHAR2の制限を超える可能性がある応答は最初からCLOBで設計し、JSON_OBJECTやJSON_ARRAYAGGなどのSQL/JSON関数のRETURNING CLOBを徹底します。ORDS側のメディアタイプはapplication/jsonを指定し、文字コードはUTF-8に統一します。
テストの着眼点
正常系のJSON整形と同じ重みで異常系を網羅します。一意制約違反、ロック競合、権限不足、想定外例外、存在しないリソースの各ケースで、HTTPステータス、ボディのcode、retryable、correlationIdが期待通りであることを確認します。並列実行下での連続リクエストに対し、同一correlationIdのログ追跡と、冪等性による最終状態の収束も合わせて検証します。
まとめ
REST対応のPL/SQLでは、例外をその場しのぎで返さず、短いドメインコードと再試行可否、相関IDを含む共通スキーマのJSONに正規化することが安定運用の土台になります。処理本体は成功か例外のみを意識し、ファサードでHTTPステータスとボディの二層を確定させます。冪等キーで再試行を安全にし、識別子と監査を整えることで、障害時の切り分けと再実行が予測可能になります。結果として、フロントもバッチも同じルールで扱える、堅牢で観測可能なAPI基盤が完成します。
