Oracle PL/SQL の開発

UCIDM が呼び出すオブジェクトを Oracle Database の PL/SQL で実装する方法を説明します。JSON を扱う JSON_VALUE と JSON_OBJECT を使うため、Oracle Database 12c 以降が対象です。

呼び出しの仕様そのものはストアドプロシージャ／ファンクションにまとめています。本ページは Oracle 固有の書き方に絞ります。

UCIDM が発行する SQL
実装例の前提
ユーザー反映プロシージャ
ユーザー取得ファンクション
グループの実装
メンバーの実装
sqlplus で動作を確認する
管理画面に登録する
つまずきやすい点

UCIDM が発行する SQL

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

-- 反映プロシージャの呼び出し (無名 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 文から参照できるスカラー型にします
オブジェクトは、外部連携設定の接続ユーザーのスキーマに作られます

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

連携先の属性名	連携元の属性名	用途
`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`

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

ユーザー反映プロシージャ

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

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つのテーブルから順に削除します。外部キー制約がある場合は、参照している側から先に削除します。

ユーザー取得ファンクション

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

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 の書式で戻しているのも同じ理由です。

グループの実装

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

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

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 のキーは、グループの属性マッピングの「連携先の属性名」にあわせます。

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;

メンバーの実装

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

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

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

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 が返れば何もしません。

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 で動作を確認する

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

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

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

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

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

SHOW ERRORS PROCEDURE ucidm_apply_user

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

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

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

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

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 を返すことも確認します。

管理画面に登録する

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

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

登録後に UCIDM がインストールを実行するので、実施しない操作の設定やドライランモードとあわせて、まず限定した範囲で連携を試すことを勧めます。ドライランモードでは反映プロシージャは呼ばれませんが、取得ファンクションは呼ばれます。

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

Unicorn Cloud ID Manager (オンプレミス版)

Oracle PL/SQL の開発

UCIDM が発行する SQL

実装例の前提

ユーザー反映プロシージャ

ユーザー取得ファンクション

グループの実装

メンバーの実装

sqlplus で動作を確認する

管理画面に登録する

つまずきやすい点

JSON_VALUE は必ず文字列を返す

型変換は TO_NUMBER と TO_DATE で書く

取得ファンクションの戻り値は 4000 バイトまで

プロシージャに COMMIT を書かない

識別子は大文字に正規化される

エラーメッセージの読み方

Keyboard shortcuts

Unicorn Cloud ID Manager (オンプレミス版)