【PL/SQL】パッケージAPI設計における互換性維持とバージョン戦略

-- 危険: 2引数呼び出しが両方に一致し得る
CREATE OR REPLACE PACKAGE order_api_bad AS
  FUNCTION place(
    p_customer_id IN NUMBER,
    p_amount      IN NUMBER,
    p_note        IN VARCHAR2 DEFAULT NULL
  ) RETURN NUMBER;

  FUNCTION place(
    p_customer_id IN NUMBER,
    p_amount      IN NUMBER
  ) RETURN NUMBER;
END order_api_bad;
/

-- 安全寄り: 公開シグネチャを1つに絞る
CREATE OR REPLACE PACKAGE order_api_v2 AS
  FUNCTION place(
    p_customer_id IN NUMBER,
    p_amount      IN NUMBER,
    p_note        IN VARCHAR2 DEFAULT NULL
  ) RETURN NUMBER;
END order_api_v2;
/

CREATE OR REPLACE PACKAGE order_api_v2 AUTHID DEFINER AS
  FUNCTION place(
    p_customer_id IN NUMBER,
    p_amount      IN NUMBER,
    p_note        IN VARCHAR2,
    p_request_id  IN VARCHAR2
  ) RETURN NUMBER;
END order_api_v2;
/

CREATE OR REPLACE PACKAGE order_api_v1 AUTHID DEFINER AS
  FUNCTION place(
    p_customer_id IN NUMBER,
    p_amount      IN NUMBER
  ) RETURN NUMBER;
END order_api_v1;
/

CREATE OR REPLACE PACKAGE BODY order_api_v1 AS
  FUNCTION place(
    p_customer_id IN NUMBER,
    p_amount      IN NUMBER
  ) RETURN NUMBER IS
  BEGIN
    RETURN order_api_v2.place(
      p_customer_id => p_customer_id,
      p_amount      => p_amount,
      p_note        => NULL,
      p_request_id  => RAWTOHEX(SYS_GUID())
    );
  END;
END order_api_v1;
/

CREATE OR REPLACE PACKAGE order_api AUTHID DEFINER AS
  FUNCTION place(
    p_customer_id IN NUMBER,
    p_amount      IN NUMBER
  ) RETURN NUMBER;
END order_api;
/

CREATE OR REPLACE PACKAGE BODY order_api AS
  FUNCTION place(
    p_customer_id IN NUMBER,
    p_amount      IN NUMBER
  ) RETURN NUMBER IS
  BEGIN
    RETURN order_api_v2.place(
      p_customer_id => p_customer_id,
      p_amount      => p_amount,
      p_note        => NULL,
      p_request_id  => RAWTOHEX(SYS_GUID())
    );
  END;
END order_api;
/

CREATE OR REPLACE PACKAGE BODY order_api_v1 AS
  FUNCTION place(
    p_customer_id IN NUMBER,
    p_amount      IN NUMBER
  ) RETURN NUMBER IS
  BEGIN
    pkg_logger.info(
      p_message     => 'DEPRECATED order_api_v1.place used',
      p_target_type => 'PACKAGE_API',
      p_target_key  => 'ORDER_API_V1.PLACE',
      p_payload     => JSON_OBJECT(
        'customer_id' VALUE p_customer_id,
        'module' VALUE SYS_CONTEXT('USERENV', 'MODULE'),
        'action' VALUE SYS_CONTEXT('USERENV', 'ACTION')
        RETURNING CLOB
      )
    );

    RETURN order_api_v2.place(
      p_customer_id => p_customer_id,
      p_amount      => p_amount,
      p_note        => NULL,
      p_request_id  => RAWTOHEX(SYS_GUID())
    );
  END;
END order_api_v1;
/

CREATE OR REPLACE FUNCTION place_order_json(
  p_customer_id IN NUMBER,
  p_amount      IN NUMBER,
  p_note        IN VARCHAR2 DEFAULT NULL
) RETURN CLOB IS
  v_order_id NUMBER;
BEGIN
  v_order_id := order_api_v2.place(
    p_customer_id => p_customer_id,
    p_amount      => p_amount,
    p_note        => p_note,
    p_request_id  => RAWTOHEX(SYS_GUID())
  );

  RETURN JSON_OBJECT(
    'schema_version' VALUE 2,
    'ok' VALUE TRUE,
    'order_id' VALUE v_order_id,
    'error_code' VALUE NULL,
    'error_detail' VALUE NULL
    RETURNING CLOB
  );
EXCEPTION
  WHEN OTHERS THEN
    RETURN JSON_OBJECT(
      'schema_version' VALUE 2,
      'ok' VALUE FALSE,
      'order_id' VALUE NULL,
      'error_code' VALUE 'E-ORDER-001',
      'error_detail' VALUE SQLERRM
      RETURNING CLOB
    );
END;
/

SELECT
  owner,
  package_name,
  object_name AS subprogram_name,
  overload,
  subprogram_id,
  sequence,
  position,
  argument_name,
  in_out,
  data_type,
  type_owner,
  type_name,
  defaulted
FROM all_arguments
WHERE owner = UPPER(:api_owner)
  AND package_name IN ('ORDER_API_V1', 'ORDER_API_V2', 'ORDER_API')
ORDER BY package_name, subprogram_id, sequence;

SELECT
  owner,
  name,
  type,
  referenced_owner,
  referenced_name,
  referenced_type
FROM all_dependencies
WHERE referenced_owner = UPPER(:api_owner)
  AND referenced_name = UPPER(:package_name)
ORDER BY owner, type, name;

SELECT owner, object_type, object_name, status
FROM all_objects
WHERE owner = UPPER(:api_owner)
  AND status = 'INVALID'
ORDER BY object_type, object_name;

-- 管理権限を持つユーザーが事前に実行
ALTER USER app ENABLE EDITIONS;

CREATE EDITION release_202606 AS CHILD OF ORA$BASE;

-- デプロイセッションを新エディションへ切り替える
ALTER SESSION SET EDITION = release_202606;

-- 新エディション内へパッケージをデプロイ
CREATE OR REPLACE PACKAGE BODY app.order_api AS
  -- 新実装
END order_api;
/

-- セッション単位で検証
ALTER SESSION SET EDITION = release_202606;
SELECT SYS_CONTEXT('USERENV', 'CURRENT_EDITION_NAME') FROM dual;

-- 検証後、必要な管理権限でデフォルトエディションを切り替える
ALTER DATABASE DEFAULT EDITION = release_202606;

DECLARE
  v_order_id_v1 NUMBER;
  v_order_id_v2 NUMBER;
BEGIN
  SAVEPOINT before_compat_test;

  DBMS_APPLICATION_INFO.SET_MODULE('API_COMPAT_TEST', 'ORDER_PLACE');

  v_order_id_v1 := order_api_v1.place(
    p_customer_id => 900001,
    p_amount      => 1000
  );

  -- v2は別のテスト顧客・一意なrequest_idで検証する
  v_order_id_v2 := order_api_v2.place(
    p_customer_id => 900002,
    p_amount      => 1000,
    p_note        => NULL,
    p_request_id  => 'TEST-' || RAWTOHEX(SYS_GUID())
  );

  IF v_order_id_v1 IS NULL OR v_order_id_v2 IS NULL THEN
    RAISE_APPLICATION_ERROR(-20001, 'order_id is null');
  END IF;

  -- 件数、金額、監査ログ、例外コードなどを比較する
  ROLLBACK TO before_compat_test;
END;
/