2025年2月26日水曜日

APEX 24.2のJSONリレーショナル二面性ビューのサポートと二面性ビューの活用について

Oracle APEX 24.2では、データ・ソースとしてJSONリレーショナル二面性ビュー（以下より二面性ビューとします）をサポートしています。この機能を確認してみます。

Oracle APEX 24.2での二面性ビューのサポートについて、オラクル社のプロダクト・マネージャーのSanthosh BVSRKさんが公式ブログに以下の記事を寄稿しています。

What's New in APEX 24.2: Leveraging JSON Sources and Duality Views for Flexible Component Development in Oracle APEX

二面性ビューの実体はリレーショナル表です。Oracle APEXはもともとレポートやチャートおよびフォームなどを用いて、データ・ソースであるリレーショナル表を扱うシステムです。Oracle APEXがデータ・ソースとしてJSONを扱う際は、JSON_TABLEファンクションを呼び出し、JSONを表形式にしています。データの実体がJSONであるJSONコレクション表や、JSON列のある表とは異なり、二面性ビューには実体であるリレーショナル表があります。APEXアプリケーションは直接リレーショナル表をデータ・ソースとして扱うため、わざわざ二面性ビューを介する必要がありません。

JSON列のある表、JSONコレクション表、二面性ビューはすべて、APEXから見るとJSONを返す表で、同じ扱いになります。JSON列のある表、JSONコレクション表のような、元々リレーショナル表でないJSONデータを、Oracle APEXのデータ・ソース（＝表形式）として宣言的に扱えるのは重要ですが、二面性ビューについては、Orace REST Data ServicesのREST APIや、MongoDB互換APIで使用するためのものと言えます。

以下より、Oracle APEX 24.2での二面性ビューの扱いを紹介します。また、Oracle APEXのアプリケーションと共に、ReactのアプリケーションでMongoDB互換APIやOracle REST Data ServicesのREST APIを使って二面性ビューを扱ってみます。

前出の記事で紹介されているDDLを呼び出し、二面性ビューDEPTEMP_SQL_DVを作成します。二面性ビューの元になるリレーショナル表は、サンプル・データセットのEMP/DEPTに含まれる表EMPとDEPTです。

create or replace json relational duality view deptemp_sql_dv as
select json{ '_id': d.deptno,
'dname': d.dname,
'loc': d.loc,
'employees':
[ select json{ 'empno': e.empno,
'ename': e.ename,
'job': e.job,
'hiredate': e.hiredate,
'compensation': {
'sal': e.sal,
'comm': e.comm
}
}
from emp e
with insert update delete
where e.deptno = d.deptno
]
}
from dept d
with insert update delete nocheck
/
view raw deptemp_sql_dv.sql hosted with ❤ by GitHub


作成した二面性ビューは、部門ごとにひとつのJSONオブジェクトとなり、その中に従業員の配列を持ちます。
{
  "_id": 10,
  "_metadata": {
    "etag": "30397889F29653D2B1CD10819C527775",
    "asof": "000000000119ECDC"
  },
  "dname": "ACCOUNTING",
  "loc": "NEW YORK",
  "employees": [
    {
      "empno": 7782,
      "ename": "CLARK",
      "job": "MANAGER",
      "hiredate": "1981-06-09T00:00:00",
      "compensation": {
        "sal": 2450,
        "comm": null
      }
    },
    {
      "empno": 7839,
      "ename": "KING",
      "job": "PRESIDENT",
      "hiredate": "1981-11-17T00:00:00",
      "compensation": {
        "sal": 5000,
        "comm": null
      }
    },
    {
      "empno": 7934,
      "ename": "MILLER",
      "job": "CLERK",
      "hiredate": "1982-01-23T00:00:00",
      "compensation": {
        "sal": 1300,
        "comm": null
      }
    }
  ]
}
二面性ビューは元になっているリレーショナル表の定義情報より、JSONスキーマを生成することができます。DBMS_JSON_SCHEMA.DESCRIBEを呼び出します。
begin
    dbms_output.put_line(json_serialize(dbms_json_schema.describe('DEPTEMP_SQL_DV') returning clob pretty));
end;

作成した二面性ビューをAPEXアプリケーションで操作します。

空のAPEXアプリケーションを作成します。名前は二面性ビューとします。


アプリケーションが作成されます。

共有コンポーネントを開き、データ・ソースとして二面性ビューを作成します。


データ・ソースの二面性ビューを開きます。


作成をクリックします。


名前をDEPTEMP_SQL_DVとし、二面性ビューにDEPTEMP_SQL_DVを選択します。

次へをクリックすると、以下のエラーが発生します。

ORA-06503: PL/SQL: ファンクションが値なしで戻されました


Oracle APEX 24.2とOracle Database 23ai 23.6以降の組み合わせで発生する不具合のようです。APEX 24.2.3で修正される予定です。

ワークアラウンドとして、二面性ビューをJSONソースとして作成します。

共有コンポーネントのJSONソースを開きます。


作成をクリックします。


作成するJSONソースの名前はDEPTEMP_SQL_DVとします。JSONソース・タイプにJSON列のある表を選択し、JSON列のある表として二面性ビューDEPTEMP_SQL_DVを選択します。

次へ進みます。


JSON列1にDATA(Json)が選択されます。二面性ビューおよびJSONコレクション表は、データ・ソースとしては、JSON型の列DATAだけを持つ表です。二面性ビューにはスキーマが定義されていますが、ここで列1のJSONスキーマとしてDBMS_JSON_SCHEMA.DESCRIBEの出力を与えると、二面性ビューを作成する際に発生したエラーORA-6503が発生します。

APEX 24.2.2では、Oracle Database 23ai 23.6が生成するJSONスキーマを扱えないようです。そのため、JSONスキーマの代わりに、すでに保存されているデータからデータ・プロファイルを作成します。

次へ進みます。


検出されたデータ・プロファイルを確認します。

主キーやデータ型は、DBMS_JSON_SCHEMA.DESCRIBEの出力、または元となる表EMPやDEPTの定義と一致させます。

データ・ソースとして扱うには最低限、主キーはC_ID（セレクタ_id）、C_METADATA_ASOF（セレクタ_metadata.asof）、C_METADATA_ETAG（セレクタ_metadata.etag）のデータ型がVarchar2となっている必要があります。C_METADATA_ASOFおよびC_METADATA_ETAGは楽観的な同時実行制御に使用されます。データ型がVarchar2でない場合は、JSONソース作成後に、データ・プロファイルを編集します。

作成をクリックします。


JSONソースとしてDEPTEMP_SQL_DVが作成されます。


作成されたJSONソースDEPTEMP_SQL_DVを開き、データ・プロファイルを編集します。


データ・プロファイルよりC_IDが主キーであること、C_METADATA_ASOFおよびC_METADATA_ETAGのデータ型がVarchar2であること、EMPLOYEESのデータ型が配列として認識されていることを確認します。


DBMS_JSON_SCHEMA.DESCRIBEを呼び出して得られる二面性ビューDEPTEMP_SQL_DVの、_metadataオブジェクトに関するJSONスキーマは以下になります。
begin
    dbms_output.put_line(json_serialize(json_query(dbms_json_schema.describe('DEPTEMP_SQL_DV'), '$.properties._metadata') returning clob pretty));
end;
{
  "etag" :
  {
    "type" : "string",
    "extendedType" : "string",
    "maxLength" : 200
  },
  "asof" :
  {
    "type" : "string",
    "extendedType" : "string",
    "maxLength" : 20
  }
}

列C_METADATA_ETAGについては、データ型はVarchar2、長さは200に設定します。


列C_METADATA_ASOFについては、データ型はVarchar2、長さは20に設定します。


以上で二面性ビューDEPTEMP_SQL_DVをJSONソースとして作成できました。

作成したJSONソースDEPTEMP_SQL_DVを使って、フォーム付き対話モード・レポートのページを作成します。

ページの作成をクリックします。


対話モード・レポートを選択します。


対話モード・レポートのページの名前はDEPTEMP_SQL_DVとし、フォーム・ページを含めるをオンにします。フォーム・ページ名はDEPTEMP_SQL_DV Detailとします。

データ・ソースにJSONソース、JSONソースとして先ほど作成したDEPTEMP_SQL_DVを選択します。DEPTEMP_SQL_DVは、表DEPTの１行をひとつのJSONオブジェクトして、その中の属性employeesに、その部門に所属している従業員を配列として持ちます。

部門ではなく従業員を編集するフォームを作成するために、ネストした行として1. EMPLOYEESを選択します。

次へ進みます。


主キー列1はC_ID (Number) - これは表DEPTの主キーDEPTNOです - 、主キー列2はEMPNO (Number)として、従業員を特定できるように主キーを設定します。

ページの作成をクリックします。


以上で二面性ビューDEPTEMP_SQL_DVをデータ・ソースとした、対話モード・レポートとフォームのページが作成できました。

ページを実行します。

ネストした行に1. EMPLOYEESを指定して、配列として含まれている従業員のデータを展開しているため、それぞれの従業員が対話モード・レポートの１行になっています。


ボタン作成をクリックします。

ネストした行に1. EMPLOYEESを指定しているため、作成や編集の対象が従業員の情報に限定されています。部門の情報は空白で、かつ、デフォルトでは編集不可のフィールドになります。

そのため、従業員を作成することはできません。


従業員の編集と削除は可能です。

JobやEnameは、データ・プロファイルでデータ型がVarchar2で長さが4000と検出されているため、ページ・アイテムのタイプとしてテキスト領域が選択されています。


二面性ビューDEPTEMP_SQL_DVは、マスター・ディテール関係を持つ表DEPTとEMPを、JSONオブジェクトとして定義しています。Oracle APEXには、マスター・ディテール関係を持つ表から、ページを作成するウィザードがあります。二面性ビューを使用する代わりに、ページ作成ウィザードを使用して、表DEPTおよびEMPを編集するページを作成してみます。

ページの作成を実行し、コンポーネントとしてマスター・ディテールを選択します。


マスター・ディテール関係の表を操作するページの形式は、積上げ、左右、ドリルダウンの３種類から選ぶことができます。

今回は左右を選択します。


マスター・ディテール・ページ名はDEPT - EMPとします。

次へ進みます。


マスター表はDEPT、プライマリ表示列はDNAME (Varchar2)、セカンダリ表示列はLOC (Varchar2)として、ディテール表にEMPを指定します。

ページの作成をクリックします。


マスター・ディテールページが作成されます。


作成されたページを実行します。

表DEPTとEMPの双方の作成、編集、削除ができるページが作成されます。


従業員の作成フォームでEmpno（従業員番号）のページ・アイテムのタイプが表示のみとなっている点だけは修正が必要です。（APEXは主キーは自動採番のサロゲート・キーを想定しているため、主キーは表示のみがデフォルトになります。）


以上のように、Oracle APEXでは二面性ビューの実体である表からページを生成できるため、データ・ソースとして二面性ビューを扱うユースケースはほとんど無いでしょう。

二面性ビューは、主にOracle APEX以外の開発ツールで使用します。

作成した二面性ビューDEPTEMP_SQL_DVを操作するReactアプリケーションを作成してみます。二面性ビューDEPTEMP_SQL_DVは、MongoDB互換APIからはコレクションとして扱うことができます。

MongoDB互換APIの有効化については、こちらの記事で紹介しています。

mongoshで接続し、コレクションdeptemp_sql_dvの内容を印刷します。

mongosh 'mongodb://[APEXワークスペース・スキーマ]:[パスワード]@localhost:27017/[APEXワークスペース・スキーマ]?authMechanism=PLAIN&authSource=$external&tls=false&retryWrites=false&loadBalanced=true'

以下のコマンドを実行します。

db.deptemp_sql_dv.find()

% mongosh 'mongodb://wksp_apexdev:ChangeMe@localhost:27017/wksp_apexdev?authMechanism=PLAIN&authSource=$external&tls=false&retryWrites=false&loadBalanced=true'

Current Mongosh Log ID: 67bd80828ca9ad530009af79

Connecting to: mongodb://<credentials>@localhost:27017/wksp_apexdev?authMechanism=PLAIN&authSource=%24external&tls=false&retryWrites=false&loadBalanced=true&serverSelectionTimeoutMS=2000&appName=mongosh+2.3.9

Using MongoDB: 4.2.14

Using Mongosh: 2.3.9


For mongosh info see: https://www.mongodb.com/docs/mongodb-shell/


wksp_apexdev> db.deptemp_sql_dv.find()

[

  {

    _id: 10,

    dname: 'ACCOUNTING',

    loc: 'NEW YORK',

    employees: [

      {

        empno: 7782,

        ename: 'CLARK',

        job: 'MANAGER',

        hiredate: ISODate('1981-06-09T00:00:00.000Z'),

        compensation: { sal: 2450, comm: null }

      },

      {

        empno: 7839,

        ename: 'KING',

        job: 'PRESIDENT',

        hiredate: ISODate('1981-11-17T00:00:00.000Z'),

        compensation: { sal: 5000, comm: null }

      },

      {

        empno: 7934,

        ename: 'MILLER',

        job: 'CLERK',

        hiredate: ISODate('1982-01-23T00:00:00.000Z'),

        compensation: { sal: 1300, comm: null }

      }

    ],

    _metadata: {

      etag: Binary.createFromBase64('MDl4ifKWU9KxzRCBnFJ3dQ==', 0),

      asof: Binary.createFromBase64('AAAAAAEa/dM=', 0)

    }

  },

  {

    _id: 20,

    dname: 'RESEARCH',

    loc: 'DALLAS',

    employees: [

      {

        empno: 7369,

        ename: 'SMITH',

        job: 'CLERK',

        hiredate: ISODate('1980-12-17T00:00:00.000Z'),

        compensation: { sal: 800, comm: null }

      },

      {

        empno: 7566,

        ename: 'JONES',

        job: 'MANAGER',

        hiredate: ISODate('1981-04-02T00:00:00.000Z'),

        compensation: { sal: 2975, comm: null }

      },

      {

        empno: 7788,

        ename: 'SCOTT',

        job: 'ANALYST',

        hiredate: ISODate('1982-12-09T00:00:00.000Z'),

        compensation: { sal: 3000, comm: null }

      },

      {

        empno: 7876,

        ename: 'ADAMS',

        job: 'CLERK',

        hiredate: ISODate('1983-01-12T00:00:00.000Z'),

        compensation: { sal: 1100, comm: null }

      },

      {

        empno: 7902,

        ename: 'FORD',

        job: 'ANALYST',

        hiredate: ISODate('1981-12-03T00:00:00.000Z'),

        compensation: { sal: 3000, comm: null }

      }

    ],

    _metadata: {

      etag: Binary.createFromBase64('F2OypsJVgPu4aGk5x6Kmrw==', 0),

      asof: Binary.createFromBase64('AAAAAAEa/dM=', 0)

    }

  },

  {

    _id: 30,

    dname: 'SALES',

    loc: 'CHICAGO',

    employees: [

      {

        empno: 7499,

        ename: 'ALLEN',

        job: 'SALESMAN',

        hiredate: ISODate('1981-02-20T00:00:00.000Z'),

        compensation: { sal: 1600, comm: 300 }

      },

      {

        empno: 7521,

        ename: 'WARD',

        job: 'SALESMAN',

        hiredate: ISODate('1981-02-22T00:00:00.000Z'),

        compensation: { sal: 1250, comm: 500 }

      },

      {

        empno: 7654,

        ename: 'MARTIN',

        job: 'SALESMAN',

        hiredate: ISODate('1981-09-28T00:00:00.000Z'),

        compensation: { sal: 1250, comm: 1400 }

      },

      {

        empno: 7698,

        ename: 'BLAKE',

        job: 'MANAGER',

        hiredate: ISODate('1981-05-01T00:00:00.000Z'),

        compensation: { sal: 2850, comm: null }

      },

      {

        empno: 7844,

        ename: 'TURNER',

        job: 'SALESMAN',

        hiredate: ISODate('1981-09-08T00:00:00.000Z'),

        compensation: { sal: 1530, comm: 20 }

      }

    ],

    _metadata: {

      etag: Binary.createFromBase64('9iUpiR0GXvm4p9J3pJATKA==', 0),

      asof: Binary.createFromBase64('AAAAAAEa/dM=', 0)

    }

  },

  {

    _id: 40,

    dname: 'OPERATIONS',

    loc: 'BOSTON',

    employees: [],

    _metadata: {

      etag: Binary.createFromBase64('PE0/aiBUftF1sJNCwG6JCg==', 0),

      asof: Binary.createFromBase64('AAAAAAEa/dM=', 0)

    }

  }

]

wksp_apexdev> 


私にはReactのアプリケーションを作るスキルはないため、ChatGPTのo3-mini-highに以下のプロンプトを与えて、アプリケーションを作成しました。

ChatGPTの履歴 - MongoDBデータ更新Reactアプリ
https://chatgpt.com/share/67bd8cd4-fe4c-8000-ae79-27f35e224dd8

最終的にserver.jsとApp.jsが作成されます。

server.js

App.js

作成されたアプリケーションを実行し、給与(Salary)を更新してみます。アプリケーションとしては動作していますが、給与を更新するとエラーが発生します。現行のMongoDB互換APIは、ETAGの扱いに不具合があり、ETAGは常に一致しません。結果として給与を更新できません。

給与を更新するには、ETAGを使った同時実行制御を無効にする必要があります。


[Reactアプリケーションの画面構成がいまいちですが、これは二面性ビューDEPTEMP_SQL_DVから得られるJSONに合わせて画面を作成しているためです。本来であれば、二面性ビューはユーザー・インターフェースに合わせて作成します。]

二面性ビューの定義にnocheck（またはnocheck etag）を追加します。表への指定に加えて、主キー列_idにも指定します。

create or replace json relational duality view deptemp_sql_dv as
select json{ '_id': d.deptno with nocheck,
'dname': d.dname,
'loc': d.loc,
'employees':
[ select json{ 'empno': e.empno,
'ename': e.ename,
'job': e.job,
'hiredate': e.hiredate,
'compensation': {
'sal': e.sal,
'comm': e.comm
}
}
from emp e
with insert update delete nocheck
where e.deptno = d.deptno
]
}
from dept d
with insert update delete nocheck
/
view raw deptemp_sql_dv_nocheck.sql hosted with ❤ by GitHub
二面性ビューDEPTEMP_SQL_DVを作り直します。


作成した二面性ビューDEPTEMP_SQL_DVを確認します。

select * from deptemp_sql_dv;

_metadataのetagがJSONオブジェクトの内容に関わらず、常に"00000000000000000000000000000000"であることが確認できます。
_metadata":{"etag":"00000000000000000000000000000000","asof":"00000000011E6381"}

server.jsにETAGの不具合を回避するコードを追加します。

データ更新時に送信するJSONのetagを"00000000000000000000000000000000"に置き換えます。
// _idに基づいてdocument全体を更新（置換更新）
app.put('/departments/:id', async (req, res) => {
  try {
    const id = parseInt(req.params.id);
    const updatedDoc = req.body; // フロントエンドから送信されるdocument全体
    updatedDoc._metadata.etag = "00000000000000000000000000000000"; // ETAGの不具合を回避するため、常に0を渡す。
    const result = await deptempCollection.replaceOne({ _id: id }, updatedDoc);
    res.json(result);
  } catch (error) {
    res.status(500).send(error);
  }
});
更新したserver.js

ETAGの不具合に対応したアプリケーションを実行し、給与と手当を更新します。

部門ACCOUNTINGの従業員CLARKの給与を4000、KINGの手当てを200、MILLERの給与を2000、手当てを100に変更しています。


ReactアプリケーションはJSONデータを送信して二面性ビューDEPTEMP_SQL_DVを更新しています。二面性ビューの実体は表EMPとDEPTなので、先ほど作成したAPEXアプリケーションから、これらの表の値を確認します。

マスター・ディテールのページよりACCOUNTINGを選択すると、Reactアプリケーションで変更した値が反映されていることが確認できます。


従業員CLARKの給与を6000、手当てを200に変更します。


Reactアプリケーションをリロードすると、CLARKの給与が6000、手当てが200に更新されていることが確認できます。


現時点ではETAGを使った同時実行制御ができないため、Reactアプリケーションからデータを更新するときに、APEXアプリケーションがすでに更新したデータを上書きする可能性があります。その点は問題なのですが、不具合が修正されれば解消します。

今回作成したReactアプリケーションは、REST APIを介してMongoDBにアクセスしています。しかし、REST APIはOracle REST Data Servicesで実装できます。つまり、server.jsは不要であり、MongoDB互換APIを呼び出すこともありません。

二面性ビューDEPTEMP_SQL_DVに対して、AutoRESTを有効にすることによりserver.jsを置き換えます。
begin
  ords.enable_object (
    p_enabled      => TRUE,
    p_schema       => 'WKSP_APEXDEV',
    p_object       => 'DEPTEMP_SQL_DV',
    p_object_type  => 'VIEW',
    p_object_alias => 'departments'
  );
  commit;
end;
/
引数p_object_aliasにdepartmentsを指定しているため、REST APIのエンドポイントは以下になります。ホスト名やORDS別名は、環境によって異なります。

http://localhost:8181/ords/apexdev/departments/


AutoRESTの場合、items属性に部門の配列が返されます。そのため、App.jsで全部門の情報を取得する部分を、以下のように変更します。
  // バックエンドから全departmentドキュメントを取得
  const fetchDepartments = async () => {
    try {
      const res = await axios.get('http://localhost:8181/ords/apexdev/departments/');
      setDepartments(res.data.items); // 部門の配列はitems属性から取り出す。
    } catch (error) {
      console.error("Error fetching departments:", error);
    }
  };
etagを"00000000000000000000000000000000"に置き換えるワークアラウンドは不要になります。また、二面性ビューからwith nocheckを外し、ETAGによる同時実行制御を有効にできます。

AutoRESTを有効にした二面性ビューにアクセスするApp.js

アプリケーションの動作確認を行います。

Reactアプリケーションより、KINGの給与を8000、手当てを8000に変更します。


APEXアプリケーションより、値の変更を確認します。従業員KINGの給与が8000、手当てが8000になっていることが確認できます。


APEXアプリケーションから従業員MILLERの給与を4050、手当てを空白に変更します。


Reactアプリケーションより、従業員CLARKの給与を2000、手当てを100に変更します。APEXアプリケーションよりMILLERの給与および手当てを更新しているため、エラーが発生し更新が拒否されます。


ページをリフレッシュして、APEXアプリケーションが更新したMILLERの値を反映させます。その上で、従業員CLARKの給与を2000、手当てを100に変更します。今度は正常に更新できます。


APEXアプリケーションに移ります。従業員CLARKの給与と手当てをReactアプリで更新した値に反映させていない状態で、従業員MILLERを編集します。


従業員MILLERの給与を3000、手当てを300に変更します。

変更の適用をクリックします。


APEXアプリケーションによる更新は成功します。

Reactアプリケーションは部門の単位でETAGが生成されています。そのため、部門に所属している従業員の誰かの情報が変更されていると、更新はエラーになります。APEXアプリケーションでは従業員毎にチェックサムが生成されているため、更新対象の従業員の情報に変更があるときだけ、更新がエラーになります。


Reactアプリケーションで同じ従業員の情報が変更されていると、APEXアプリケーション側でも更新エラーが発生します。


今回の記事は以上になります。

Oracle APEX 24.2での二面性ビューのサポートと、Oracle APEXのアプリケーションと共に、二面性ビューをどのように活用するかについて紹介しました。

Oracle APEXのアプリケーション作成の参考になれば幸いです。

完
時刻: