> For AI agents: the complete documentation index is available at /resource/ucidm/docs/on-premise/dev/llms.txt, the full documentation bundle is available at /resource/ucidm/docs/on-premise/dev/llms-full.txt.

# Oracle PL/SQL の開発

[UCIDM が呼び出すオブジェクト](/resource/ucidm/docs/on-premise/dev/tech/storedProcedure/index.md#ucidm-objects) を Oracle Database の PL/SQL で実装する方法を説明します。JSON を扱う `JSON_VALUE` と `JSON_OBJECT` を使うため、Oracle Database 12c 以降が対象です。

呼び出しの仕様そのものは [ストアドプロシージャ／ファンクション](/resource/ucidm/docs/on-premise/dev/tech/storedProcedure/index.md) にまとめています。本ページは Oracle 固有の書き方に絞ります。

## UCIDM が発行する SQL \{#generated-sql}

実装の制約は、UCIDM が発行する SQL から決まります。Oracle に対しては次の3種類を発行します。

```sql
-- 反映プロシージャの呼び出し (無名 PL/SQL ブロック)
BEGIN ucidm_apply_user(:arg1); END;

-- 取得ファンクションの呼び出し
SELECT ucidm_get_user(:arg1) FROM dual;

-- インストール前の削除 (ORA-04043 だけを握りつぶす)
BEGIN
  EXECUTE IMMEDIATE 'DROP PROCEDURE ucidm_apply_user';
EXCEPTION WHEN OTHERS THEN IF SQLCODE != -4043 THEN RAISE; END IF;
END;
```

ここから、Oracle での実装は次の形に決まります。

- パッケージではなく、単独のプロシージャとファンクションとして作成します
- 引数はちょうど1つで、型は `VARCHAR2` です
- 取得ファンクションの戻り値は SQL 文から参照できるスカラー型にします
- オブジェクトは、外部連携設定の接続ユーザーのスキーマに作られます

連携先のテーブルが別のスキーマにある場合は、プロシージャ内でスキーマ名を修飾するか、シノニムとオブジェクト権限を用意してください。

## 接続ユーザーの権限 \{#privileges}

[接続ユーザーに要る権限](/resource/ucidm/docs/on-premise/dev/tech/storedProcedure/index.md#privileges) のうち、Oracle で明示的に与える必要があるのは作成の権限だけです。`CREATE PROCEDURE` システム権限があれば、自分のスキーマにプロシージャとファンクションの両方を作成できます。自分のスキーマのオブジェクトを削除したり実行したりするのに追加の権限は要りません。

```sql
GRANT CREATE SESSION TO ucidm;
GRANT CREATE PROCEDURE TO ucidm;
```

プロシージャが読み書きするテーブルが接続ユーザー自身のスキーマにあれば、これで足ります。別のスキーマにある場合は、そのテーブルへのオブジェクト権限を接続ユーザーに与えます。

```sql
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.emp_user TO ucidm;
```

このオブジェクト権限はロール経由では与えられません。PL/SQL のプロシージャとファンクションは既定で定義者権限 (definer's rights) で動き、その中ではロールが無効になるためです。ロールに権限を含めても、プロシージャのコンパイル時に表が見えず、インストールに失敗します。sqlplus からは同じ表を読めるのにインストールだけが失敗する場合は、これを疑ってください。接続ユーザーに直接 `GRANT` すれば解決します。

## 実装例の前提 \{#example-schema}

社員管理の既存スキーマに ID 連携する例で説明します。連携先には次のテーブルがあるものとします。

```sql
-- ユーザーの基本情報
CREATE TABLE emp_user (
  user_name    VARCHAR2(255) PRIMARY KEY,
  display_name VARCHAR2(1000) NOT NULL,
  status       VARCHAR2(50),
  emp_no       NUMBER,
  pwd_changed  DATE,
  dept_code    VARCHAR2(50)
);

-- メールアドレスは別テーブル
CREATE TABLE emp_contact (
  user_name VARCHAR2(255) PRIMARY KEY,
  email     VARCHAR2(255)
);

-- パスワードも別テーブル
CREATE TABLE emp_cred (
  user_name VARCHAR2(255) PRIMARY KEY,
  password  VARCHAR2(255)
);

-- グループは連番の代理キーが主キー
CREATE TABLE emp_group_seq (
  id         NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  primary_id VARCHAR2(255) UNIQUE
);
CREATE TABLE emp_group (
  id    NUMBER PRIMARY KEY,
  label VARCHAR2(255)
);
CREATE TABLE emp_member (
  group_name VARCHAR2(255),
  user_name  VARCHAR2(255),
  PRIMARY KEY (group_name, user_name)
);

-- 部署名から部署コードへの変換マスタ
CREATE TABLE emp_dept_code (
  name VARCHAR2(255) PRIMARY KEY,
  code VARCHAR2(255)
);
INSERT INTO emp_dept_code(name, code) VALUES ('営業部', 'D001');
INSERT INTO emp_dept_code(name, code) VALUES ('開発部', 'D002');
INSERT INTO emp_dept_code(name, code) VALUES ('人事部', 'D003');
COMMIT;
```

このスキーマは、手続き型 SQL でなければ扱いにくい要素を集めてあります。1つのユーザーが3つのテーブルに分かれること、部署は名前ではなくコードで持つこと、グループの主キーが外部の識別子ではなく連番であること、そして数値と日付のカラムがあることです。

属性マッピングは次のように設定したものとします。左の列が反映プロシージャの `attrs` のキーになります。

| 連携先の属性名            | 連携元の属性名            | 用途                           |
| :----------------- | :----------------- | :--------------------------- |
| `cn`               | `cn`               | `emp_user.display_name`      |
| `mail`             | `mail`             | `emp_contact.email`          |
| `empNo`            | `employeeNumber`   | `emp_user.emp_no` (数値)       |
| `pwdChanged`       | `pwdChangedTime`   | `emp_user.pwd_changed` (日付)  |
| `department`       | `ou`               | `emp_user.dept_code` (マスタ変換) |
| `ucidmUserEnabled` | `ucidmUserEnabled` | `emp_user.status`            |

グループの属性マッピングは `label` に `cn` を割り当てます。

最後の `ucidmUserEnabled` は、JSON の `enabled` を解決するために必要です。動作設定で「ユーザーステータス設定情報を使用する」を off にした場合、UCIDM は「ステータス用属性名」に指定した名前を**マッピング後の属性**から探します。属性マッピングにこの行がないと `enabled` が解決されず、JSON に含まれません。「ユーザーステータス設定情報を使用する」を on にした場合はマッピングは不要です。

## ユーザー反映プロシージャ \{#apply-user}

`opCode` で4つの操作を分岐します。

```sql
CREATE PROCEDURE ucidm_apply_user(p VARCHAR2) AS
  op  VARCHAR2(8);
  pid VARCHAR2(255);
BEGIN
  op  := JSON_VALUE(p, '$.opCode');
  pid := JSON_VALUE(p, '$.primaryID');

  IF op = 'I' THEN
    INSERT INTO emp_user(user_name, display_name, status, emp_no, pwd_changed, dept_code)
      VALUES (pid,
              JSON_VALUE(p, '$.attrs.cn'),
              CASE WHEN JSON_VALUE(p, '$.enabled') = 'true' THEN 'active' ELSE 'inactive' END,
              TO_NUMBER(JSON_VALUE(p, '$.attrs.empNo')),
              TO_DATE(JSON_VALUE(p, '$.attrs.pwdChanged'), 'YYYYMMDDHH24MISS"Z"'),
              (SELECT code FROM emp_dept_code WHERE name = JSON_VALUE(p, '$.attrs.department')));
    INSERT INTO emp_contact(user_name, email)
      VALUES (pid, JSON_VALUE(p, '$.attrs.mail'));

  ELSIF op = 'U' THEN
    UPDATE emp_user SET
      display_name = COALESCE(JSON_VALUE(p, '$.attrs.cn'), display_name),
      emp_no       = COALESCE(TO_NUMBER(JSON_VALUE(p, '$.attrs.empNo')), emp_no),
      pwd_changed  = COALESCE(TO_DATE(JSON_VALUE(p, '$.attrs.pwdChanged'), 'YYYYMMDDHH24MISS"Z"'), pwd_changed),
      dept_code    = COALESCE((SELECT code FROM emp_dept_code
                                 WHERE name = JSON_VALUE(p, '$.attrs.department')), dept_code),
      status       = CASE WHEN JSON_VALUE(p, '$.enabled') IS NOT NULL
                          THEN (CASE WHEN JSON_VALUE(p, '$.enabled') = 'true' THEN 'active' ELSE 'inactive' END)
                          ELSE status END
      WHERE user_name = pid;
    IF JSON_VALUE(p, '$.attrs.mail') IS NOT NULL THEN
      MERGE INTO emp_contact d
        USING (SELECT pid AS user_name, JSON_VALUE(p, '$.attrs.mail') AS email FROM dual) s
        ON (d.user_name = s.user_name)
        WHEN MATCHED THEN UPDATE SET d.email = s.email
        WHEN NOT MATCHED THEN INSERT (user_name, email) VALUES (s.user_name, s.email);
    END IF;

  ELSIF op = 'P' THEN
    MERGE INTO emp_cred d
      USING (SELECT pid AS user_name, JSON_VALUE(p, '$.attrs.password') AS password FROM dual) s
      ON (d.user_name = s.user_name)
      WHEN MATCHED THEN UPDATE SET d.password = s.password
      WHEN NOT MATCHED THEN INSERT (user_name, password) VALUES (s.user_name, s.password);

  ELSIF op = 'D' THEN
    DELETE FROM emp_contact WHERE user_name = pid;
    DELETE FROM emp_cred WHERE user_name = pid;
    DELETE FROM emp_user WHERE user_name = pid;
  END IF;
END;
```

分岐ごとの要点を順に見ます。

`I` では、`attrs` の文字列を `TO_NUMBER` と `TO_DATE` でカラムの型に変換しています。`pwdChanged` の元になる LDAP の `pwdChangedTime` は `20260220213129Z` という generalizedTime 形式なので、書式マスク `YYYYMMDDHH24MISS"Z"` で解釈します。部署は `emp_dept_code` を副問い合わせで引いてコードに変換しています。

`status` は `enabled` から導いています。この書き方では、`enabled` が渡らなかったときも `ELSE` に落ちて `inactive` で登録される点に注意してください。属性マッピングの設定漏れで `enabled` が解決されないと、全員が無効な状態で登録されます。未解決のときに別の値を入れたい場合は、`JSON_VALUE(p, '$.enabled') IS NULL` の場合を分けてください。

`U` では、すべてのカラムを `COALESCE` で包んでいます。更新イベントで届く `attrs` には変更のあった属性しか入らないため、これがないと今回渡されなかったカラムが NULL で上書きされます。`status` だけは `COALESCE` ではなく `CASE` で書いています。`enabled` は真偽値なので、`false` を「値なし」と区別する必要があるためです。

`P` はパスワード更新です。`emp_cred` に行があるとは限らないので `MERGE` で挿入と更新を兼ねています。パスワードマッピングを設定していない場合、`$.attrs.password` には平文が入ります。連携先の要件にあわせて、パスワードマッピングでハッシュ化するか、プロシージャ内でハッシュ化してください。

`D` では3つのテーブルから順に削除します。外部キー制約がある場合は、参照している側から先に削除します。

## ユーザー取得ファンクション \{#get-user}

`JSON_OBJECT` で結果を組み立て、行がなければ NULL を返します。

```sql
CREATE FUNCTION ucidm_get_user(p VARCHAR2) RETURN VARCHAR2 AS
  result VARCHAR2(4000);
BEGIN
  SELECT JSON_OBJECT(
           'cn'         VALUE u.display_name,
           'mail'       VALUE c.email,
           'empNo'      VALUE u.emp_no,
           'pwdChanged' VALUE TO_CHAR(u.pwd_changed, 'YYYYMMDDHH24MISS"Z"'),
           'department' VALUE d.name)
    INTO result
    FROM emp_user u
           LEFT JOIN emp_contact c ON c.user_name = u.user_name
           LEFT JOIN emp_dept_code d ON d.code = u.dept_code
    WHERE u.user_name = JSON_VALUE(p, '$.primaryID');
  RETURN result;
EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL;
END;
```

`SELECT ... INTO` は行が1件もないと `NO_DATA_FOUND` を送出します。これを例外ハンドラで捕まえて NULL を返すのが、Oracle での「対象が存在しない」の表し方です。この例外を捕まえ忘れると、新規ユーザーの追加がすべてエラーになります。

JSON のキーは、属性マッピングの「連携先の属性名」にあわせています。名前だけでなく値の形もあわせると、連携履歴の差分が読める形になります。この例では `department` を `emp_dept_code` と結合して部署名に戻しています。カラムに入っている `dept_code` をそのまま返すと、変更前がコード、変更後が部署名という差分になり、比較になりません。`pwdChanged` を generalizedTime の書式で戻しているのも同じ理由です。

## グループの実装 \{#group}

グループはユーザーと同じくエントリです。`primaryID` で識別し、属性マッピングの結果を `attrs` で受け取ります。ユーザーとの違いは2つあります。`enabled` が渡らないことと、パスワード更新の `P` が来ないことです。反映プロシージャが分岐するのは `I`、`U`、`D` の3つになります。

グループの反映プロシージャは、外部の識別子と連番の代理キーを対応づけます。`RETURNING ... INTO` で採番結果を受け取り、それを `emp_group` の主キーにします。

```sql
CREATE PROCEDURE ucidm_apply_group(p VARCHAR2) AS
  op  VARCHAR2(8);
  pid VARCHAR2(255);
  gid NUMBER;
BEGIN
  op  := JSON_VALUE(p, '$.opCode');
  pid := JSON_VALUE(p, '$.primaryID');

  IF op = 'I' THEN
    INSERT INTO emp_group_seq(primary_id) VALUES (pid) RETURNING id INTO gid;
    INSERT INTO emp_group(id, label) VALUES (gid, JSON_VALUE(p, '$.attrs.label'));
  ELSIF op = 'U' THEN
    UPDATE emp_group SET label = COALESCE(JSON_VALUE(p, '$.attrs.label'), label)
      WHERE id = (SELECT id FROM emp_group_seq WHERE primary_id = pid);
  ELSIF op = 'D' THEN
    DELETE FROM emp_member WHERE group_name = pid;
    DELETE FROM emp_group WHERE id = (SELECT id FROM emp_group_seq WHERE primary_id = pid);
    DELETE FROM emp_group_seq WHERE primary_id = pid;
  END IF;
END;
```

グループ取得ファンクションはユーザーと同じ形です。返す JSON のキーは、グループの属性マッピングの「連携先の属性名」にあわせます。

```sql
CREATE FUNCTION ucidm_get_group(p VARCHAR2) RETURN VARCHAR2 AS
  result VARCHAR2(4000);
BEGIN
  SELECT JSON_OBJECT('id' VALUE s.id, 'label' VALUE g.label)
    INTO result
    FROM emp_group_seq s JOIN emp_group g ON g.id = s.id
    WHERE s.primary_id = JSON_VALUE(p, '$.primaryID');
  RETURN result;
EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL;
END;
```

## メンバーの実装 \{#member}

メンバーはエントリではなく、グループとユーザーの関係です。属性を持たないため `attrs` も `enabled` も渡らず、グループとメンバーの2つの `primaryID` の組だけで識別します。`opCode` も追加の `I` と削除の `D` の2つだけで、更新に相当する操作はありません。

メンバーの追加と削除では、UCIDM はまずグループ取得ファンクションでグループの存在を確認します。グループが見つからなければエラーになり、メンバー反映プロシージャは呼ばれません。

反映プロシージャは追加と削除を分岐するだけです。UCIDM は追加前にメンバー取得ファンクションで重複を確認しますが、他の経路で登録された行と衝突しないように `MERGE` で書いています。

```sql
CREATE PROCEDURE ucidm_apply_member(p VARCHAR2) AS
  op   VARCHAR2(8);
  gpid VARCHAR2(255);
  mpid VARCHAR2(255);
BEGIN
  op   := JSON_VALUE(p, '$.opCode');
  gpid := JSON_VALUE(p, '$.groupPrimaryID');
  mpid := JSON_VALUE(p, '$.memberPrimaryID');

  IF op = 'I' THEN
    MERGE INTO emp_member d
      USING (SELECT gpid AS group_name, mpid AS user_name FROM dual) s
      ON (d.group_name = s.group_name AND d.user_name = s.user_name)
      WHEN NOT MATCHED THEN INSERT (group_name, user_name) VALUES (s.group_name, s.user_name);
  ELSIF op = 'D' THEN
    DELETE FROM emp_member WHERE group_name = gpid AND user_name = mpid;
  END IF;
END;
```

メンバー取得ファンクションの戻り値は存在確認にしか使われません。行の有無さえ判別できれば、JSON の中身は何でもかまいません。追加時に JSON が返れば登録済みとしてスキップし、削除時に NULL が返れば何もしません。

```sql
CREATE FUNCTION ucidm_get_member(p VARCHAR2) RETURN VARCHAR2 AS
  result VARCHAR2(4000);
BEGIN
  SELECT JSON_OBJECT('group_name' VALUE m.group_name, 'user_name' VALUE m.user_name)
    INTO result
    FROM emp_member m
    WHERE m.group_name = JSON_VALUE(p, '$.groupPrimaryID')
      AND m.user_name  = JSON_VALUE(p, '$.memberPrimaryID');
  RETURN result;
EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL;
END;
```

## sqlplus で動作を確認する \{#verify}

管理画面に登録する前に、sqlplus で作成して手で呼び出すのが確実です。UCIDM が発行するのと同じ形の SQL を実行できます。

```bash
$ sqlplus system/password@//localhost:1521/FREEPDB1
```

sqlplus では PL/SQL ブロックの終わりを `/` で示します。定義の後ろに `/` だけの行を置いてください。

```sql
CREATE PROCEDURE ucidm_apply_user(p VARCHAR2) AS
...
END;
/
```

作成に失敗しても sqlplus は `Warning: Procedure created with compilation errors.` としか表示しません。エラーの内容は `SHOW ERRORS` で確認します。

```sql
SHOW ERRORS PROCEDURE ucidm_apply_user

SELECT line, position, text FROM user_errors
 WHERE name = 'UCIDM_APPLY_USER' ORDER BY sequence;
```

6つのオブジェクトができているかは `user_objects` で確認します。

```sql
SELECT object_name, object_type, status FROM user_objects
 WHERE object_name LIKE 'UCIDM\_%' ESCAPE '\' ORDER BY object_name;
```

反映と取得を手で呼び出して、期待どおりのデータが入るかを確認します。

```sql
BEGIN ucidm_apply_user('{"opCode":"I","primaryID":"user01","enabled":true,
  "attrs":{"cn":"山田太郎","mail":"taro@example.com","empNo":"1042",
  "pwdChanged":"20260220213129Z","department":"営業部"}}'); END;
/

SELECT ucidm_get_user('{"primaryID":"user01"}') FROM dual;
```

`opCode` を `U`、`P`、`D` に変えて、更新と削除も同じ手順で確認してください。存在しない `primaryID` に対して取得ファンクションが NULL を返すことも確認します。

## 管理画面に登録する \{#register}

動作を確認できたら、CREATE 文を管理画面の対応する項目に貼り付けます。sqlplus で使った定義から `/` を取り除いてください。UCIDM は登録された文字列を1つの文としてそのまま実行するため、`/` があると構文エラーになります。末尾の `END;` のセミコロンは PL/SQL ブロックの一部なので必要です。

UCIDM が先に DROP するので `CREATE OR REPLACE` にする必要はありません。

登録後に UCIDM がインストールを実行するので、[実施しない操作の設定](/resource/ucidm/docs/on-premise/dev/administration/admin-ui/destination/rdbms.md#skip-operations) やドライランモードとあわせて、まず限定した範囲で連携を試すことを勧めます。ドライランモードでは反映プロシージャは呼ばれませんが、取得ファンクションは呼ばれます。

## つまずきやすい点 \{#pitfalls}

### JSON_VALUE は必ず文字列を返す \{#json-value-returns-text}

`enabled` は JSON の真偽値ですが、`JSON_VALUE(p, '$.enabled')` が返すのは `'true'` または `'false'` という文字列です。`= 'true'` で比較します。パスが存在しない場合は NULL を返すので、`IS NOT NULL` で「キーが渡されたかどうか」を判定できます。

### 型変換は TO_NUMBER と TO_DATE で書く \{#type-conversion}

`JSON_VALUE(p, '$.attrs.empNo' RETURNING NUMBER)` と書くこともできますが、勧めません。`JSON_VALUE` の既定の動作は変換に失敗したときに NULL を返すことなので、不正な値が黙って NULL として格納されます。`TO_NUMBER` や `TO_DATE` で変換すれば、失敗は例外になり、連携履歴にエラーとして残ります。

### 取得ファンクションの戻り値は 4000 バイトまで \{#varchar2-limit}

取得ファンクションは `SELECT` から呼ばれるため、戻り値は SQL の `VARCHAR2` の上限を受けます。`MAX_STRING_SIZE` が `STANDARD` の場合は 4000 バイトです。日本語の値が多いと超えることがあります。

超える場合は、返す項目を必要なものだけに絞ってください。取得ファンクションの結果は存在確認と履歴の差分表示にしか使われないので、連携している属性以外を返す必要はありません。それでも収まらない場合は `CLOB` を返す方法があります。UCIDM は `CLOB` を文字列として読みます。

### プロシージャに COMMIT を書かない \{#transaction}

UCIDM は反映プロシージャの呼び出しごとに自動コミットします。プロシージャ内に `COMMIT` や `ROLLBACK` を書くと、1回の呼び出しが1つのトランザクションという境界が崩れます。複数テーブルへの書き込みも、例外を送出すればまとめてロールバックされます。

ユーザーの追加とグループメンバーの追加は別々の呼び出しなので、別々のトランザクションになります。両者をまたぐ一貫性が必要な場合は、プロシージャの中で完結するように設計してください。

### 識別子は大文字に正規化される \{#identifier-case}

引用符で囲まない識別子は大文字として扱われます。`ucidm_apply_user` で作成したオブジェクトは `user_objects` では `UCIDM_APPLY_USER` として見えます。UCIDM は小文字の名前で `DROP` と `CALL` を発行しますが、同じ正規化が働くため一致します。二重引用符で囲んで小文字のまま作成すると、UCIDM からは見つかりません。

`UID` や `SIZE` は予約語なのでカラム名に使えません。既存スキーマにこうしたカラム名がある場合は、二重引用符で囲むか別名を使います。

### エラーメッセージの読み方 \{#error-message}

プロシージャ内で発生したエラーは、Oracle が `ORA-06512: at "SYSTEM.UCIDM_APPLY_USER", line 12` という行番号つきのバックトレースを付けて返します。UCIDM の連携履歴にはこのメッセージがそのまま記録されるので、どの文で失敗したかを追えます。

連携先の都合で反映できないデータを検出したい場合は、`RAISE_APPLICATION_ERROR` で自分のメッセージを返せます。

```sql
IF JSON_VALUE(p, '$.attrs.cn') IS NULL THEN
  RAISE_APPLICATION_ERROR(-20001, 'cn is required');
END IF;
```