Oracle Database 23aiで追加されたMLE JavaScriptにおけるJSONデータ型を扱う機能を試してみた

Oracle Database 23aiではMultilingual Engine(以下MLE)のJavaScriptに関する機能が大幅に強化されています。
21cでもJavaScriptをOracle Database上で動かす機能はありましたが、23aiではさらに使い勝手が良くなっています。
21cではアドホックな形でしか使えず、再利用するためにコードを保存しておく機能がありませんでした。
23aiでは一度実装したコードをスキーマ・オブジェクトとして保存し、任意のタイミングで呼び出したり、他のコードへimportできるようになっています。

本記事ではやや応用的な内容を扱いますが、MLEを使う上での第一歩に関しては以下が参考になります。

• オラクルエンジニア通信 - Oracle Database 23c Free - Developer ReleaseにおけるJavaScriptの概要
• オラクルエンジニア通信 - Oracle Database 23c Free - Developer ReleaseのJavaScriptコミュニティ・モジュールの使用

また私のブログでも纏めております。

• Oracle Database 23cで追加されたJavaScript関連の機能をFree版で試してみた その1
• Oracle Database 23cで追加されたJavaScript関連の機能をFree版で試してみた その2(モジュールimport、SQL DriverによるDB問合せ)

今回はMLEのJavaScriptにおけるJSONデータ向け機能も強化されたということで、それに関わる検証をやってみました。
マニュアルの新機能一覧のうち、該当箇所は以下になります。
Multilingual Engine JavaScript Support for JSON Data Type
Use JavaScript with JSON Data

今回実施した検証項目は以下の3点です。それぞれについて手順とログを載せていきます。

• JSON Relational Dualityのビューから読み込んだJSONデータをMLE JavaScript Moduleで処理する
• Binary JSON Dataから読み込んだJSONデータをMLE JavaScript Moduleで処理する
• PL/SQLから入力されたIN OUT型のJSONデータをMLE JavaScript Moduleで処理し、もとのPL/SQLに処理結果を返す
• MLE Moduleではなく動的MLEで実装したJavaScriptにPL/SQLからJSONデータを入力し、もとのPL/SQLに処理結果を返す

JSON Relational Dualityのビューから読み込んだJSONデータをMLE JavaScript Moduleで処理する

事前準備

1つめはJSON Relational Dualityビューです。
こちらは23aiで追加されたもので、目玉機能の一つとなっています。
下記マニュアルに従い、JSON Relational Dualityビューのサンプルを作成します。
2.4.1 Creating Car-Racing Duality Views Using SQL
コマンドが非常に長いため、本記事では割愛しますが、以下のオブジェクトを作成します。

• 通常のヒープ表
  • team
  • driver
  • race
  • driver_race_map
• JSON Relational Dualityビュー
  • team_dv
  • driver_dv
  • race_dv

サンプルデータを追加する手順まで実施します。

検証

以下の処理をMLE JavaScript Moduleで実装します。

1. team_dvからJSONデータ型のデータを読込み
2. 取得したデータに格納されているチーム、およびドライバーの情報を標準出力

なおteam_dvからデータ取得するにはJavaScript用のOracle Database対応したドライバが必要ですが、それはMLEにデフォルトで付属する「mle-js-oracledb」というModuleをimportして使います。

CREATE OR REPLACE MLE MODULE select_json_relational_dv_mod LANGUAGE JAVASCRIPT AS
 
import oracledb from "mle-js-oracledb";
 
function selectJRDV() {
    let connection;
 
    try {
        // Oracle Databaseに接続
        connection = oracledb.defaultConnection();
 
        // クエリ実行
        const results = connection.execute(`
            select * from team_dv`,
            [],
            {
                outFormat: oracledb.OUT_FORMAT_OBJECT
            }
        );
        
        // Oracle DatabaseのJSONデータ型はそのままJavaScriptオブジェクトとして扱える
        for (let row of results.rows) {
            console.log(`--------------
                teamId: ${row.DATA.teamId}
                name: ${row.DATA.name}
                points: ${row.DATA.points}`);
            
            for (let driverInfo of row.DATA.driver) {
                console.log(`---
                    driverId: ${driverInfo.driverId}
                    driver name: ${driverInfo.name}
                    driver points: ${driverInfo.points}`);
            }
        }
    } catch (err) {
        console.error(`an error occurred while processing the query: ${err.message}`);
    }
}
 
export { selectJRDV }
/

CREATE OR REPLACE PROCEDURE select_json_relational_dv_proc
AS MLE MODULE select_json_relational_dv_mod
SIGNATURE 'selectJRDV()';
/

上記コード例から、MLE JavaScript ModuleにおいてJSON Relational Dualityビューから取得したJSONデータ型のデータは、そのままJavaScriptオブジェクトとして扱えることが分かります。
それでは、作成したMLE Moduleを実行してみます。

SQL> set serveroutput on
SQL> EXEC select_json_relational_dv_proc();
--------------
teamId: 301
name: Red Bull
points: 0
---
driverId: 101
driver name: Max Verstappen
driver points: 0
---
driverId: 102
driver name: Sergio Perez
driver points: 0
--------------
teamId: 302
name: Ferrari
points: 0
---
driverId: 103
driver name: Charles Leclerc
driver points: 0
---
driverId: 104
driver name: Carlos Sainz Jr
driver points: 0
--------------
teamId: 303
name: Mercedes
points: 0
---
driverId: 105
driver name: George Russell
driver points: 0
---
driverId: 106
driver name: Lewis Hamilton
driver points: 0

PL/SQLプロシージャが正常に完了しました。

上記の通り、Oracle DatabaseのJSONデータ型を、JavaScriptオブジェクトとして正しく読み込めていることが分かりました。

Binary JSON Dataから読み込んだJSONデータをMLE JavaScript Moduleで処理する

前項とほぼ同じですが、次はBinary JSON Data型のデータを使って同じ検証をしてみます。
Binary JSON Data型は21cで追加された機能で、テキスト形式ではなくSQL問合せに最適化されたフォーマットでJSONを管理するデータ型です。

事前準備

Binary JSON Data型のサンプルデータを作成します。

CREATE TABLE j_purchaseorder
(id          VARCHAR2 (32) NOT NULL PRIMARY KEY,
 date_loaded TIMESTAMP (6) WITH TIME ZONE,
 po_document JSON);

INSERT INTO j_purchaseorder
VALUES (
  SYS_GUID(),
  to_date('2014-12-30'),
  '{"PONumber"             : 1600,
    "Reference"            : "ABULL-20140421",
    "Requestor"            : "Alexis Bull",
    "User"                 : "ABULL",
    "CostCenter"           : "A50",
    "ShippingInstructions" :
      {"name"    : "Alexis Bull",
       "Address" : {"street"  : "200 Sporting Green",
                    "city"    : "South San Francisco",
                    "state"   : "CA",
                    "zipCode" : 99236,
                    "country" : "United States of America"},
       "Phone"   : [{"type" : "Office", "number" : "909-555-7307"},
                    {"type" : "Mobile", "number" : "415-555-1234"}]},
    "Special Instructions" : null,
    "AllowPartialShipment" : true,
    "LineItems"            :
      [{"ItemNumber" : 1,
        "Part"       : {"Description" : "One Magic Christmas",
                        "UnitPrice"   : 19.95,
                        "UPCCode"     : 13131092899},
        "Quantity"   : 9.0},
       {"ItemNumber" : 2,
        "Part"       : {"Description" : "Lethal Weapon",
                        "UnitPrice"   : 19.95,
                        "UPCCode"     : 85391628927},
        "Quantity"   : 5.0}]}');

INSERT INTO j_purchaseorder
VALUES (
  SYS_GUID(),
  to_date('2014-12-30'),
  '{"PONumber"             : 672,
    "Reference"            : "SBELL-20141017",
    "Requestor"            : "Sarah Bell",
    "User"                 : "SBELL",
    "CostCenter"           : "A50",
    "ShippingInstructions" : {"name"    : "Sarah Bell",
                              "Address" : {"street"  : "200 Sporting Green",
                                           "city"    : "South San Francisco",
                                           "state"   : "CA",
                                           "zipCode" : 99236,
                                           "country" : "United States of America"},
                              "Phone"   : "983-555-6509"},
    "Special Instructions" : "Courier",
    "LineItems"            :
      [{"ItemNumber" : 1,
        "Part"       : {"Description" : "Making the Grade",
                        "UnitPrice"   : 20,
                        "UPCCode"     : 27616867759},
        "Quantity"   : 8.0},
       {"ItemNumber" : 2,
        "Part"       : {"Description" : "Nixon",
                        "UnitPrice"   : 19.95,
                        "UPCCode"     : 717951002396},
        "Quantity"   : 5},
       {"ItemNumber" : 3,
        "Part"       : {"Description" : "Eric Clapton: Best Of 1981-1999",
                        "UnitPrice"   : 19.95,
                        "UPCCode"     : 75993851120},
        "Quantity"   : 5.0}]}');

commit;

以上でJSONデータ型の po_document というカラムを持った j_purchaseorder 表を作成し、サンプルデータを追加しました。

検証

以下の処理を実行するMLE JavaScript Moduleを作成します。

1. j_purchaseorder 表からJSONデータ型の po_document 列を読込み
2. po_document 列の内容を標準出力

CREATE OR REPLACE MLE MODULE select_binary_json_mod LANGUAGE JAVASCRIPT AS
 
import oracledb from "mle-js-oracledb";
 
function selectBinJson() {
    let connection;
 
    try {
        // Oracle Databaseに接続
        connection = oracledb.defaultConnection();
 
        // クエリ実行
        const results = connection.execute(`
            select PO_DOCUMENT from j_purchaseorder`,
            [],
            {
                outFormat: oracledb.OUT_FORMAT_OBJECT
            }
        );

        // Oracle DatabaseのJSONデータ型はそのままJavaScriptオブジェクトとして扱える
        for (let row of results.rows) {
            console.log(`--------------
                PONumber: ${row.PO_DOCUMENT.PONumber}
                Reference: ${row.PO_DOCUMENT.Reference}
                Requestor: ${row.PO_DOCUMENT.Requestor}`);
            
            for (let item of row.PO_DOCUMENT.LineItems) {
                console.log(`---
                    ItemNumber: ${item.ItemNumber}
                    Description: ${item.Part.Description}
                    UnitPrice: ${item.Part.UnitPrice}`);
            }
        }
    } catch (err) {
        console.error(`an error occurred while processing the query: ${err.message}`);
    }
}
 
export { selectBinJson }
/

CREATE OR REPLACE PROCEDURE select_binary_json_proc
AS MLE MODULE select_binary_json_mod
SIGNATURE 'selectBinJson()';
/

JSON Relational Dualityビューと同様に、読込んだ po_document 列のJSONデータ型はそのままJavaScriptオブジェクトとして扱えます。
作成したMLE Moduleを実行してみます。

SQL> set serveroutput on
SQL> EXEC select_binary_json_proc();
--------------
PONumber: 1600
Reference: ABULL-20140421
Requestor: Alexis Bull
---
ItemNumber: 1
Description: One Magic Christmas
UnitPrice: 19.95
---
ItemNumber: 2
Description: Lethal Weapon
UnitPrice: 19.95
--------------
PONumber: 672
Reference: SBELL-20141017
Requestor: Sarah Bell
---
ItemNumber: 1
Description: Making the Grade
UnitPrice: 20
---
ItemNumber: 2
Description: Nixon
UnitPrice: 19.95
---
ItemNumber: 3
Description: Eric Clapton: Best Of 1981-1999
UnitPrice: 19.95

PL/SQLプロシージャが正常に完了しました。

MLE JavaScript Module内でJavaScriptにより実装したロジックの通り、po_document 列に保存されている注文内容が列挙されました。

PL/SQLから入力されたIN OUT型のJSONデータをMLE JavaScript Moduleで処理し、もとのPL/SQLに処理結果を返す

本項ではPL/SQL内で定義されたJSONオブジェクトをMLE JavaScript Moduleに連携し、何らかの処理をした上でPL/SQLに返す、という動作を検証してみます。
データ連携にあたってはIN OUT型の変数を使うパターンと、念のためRETURNで値を返すパターンの2つで検証してみました。

まずはMLE JavaScript Moduleを作成します。
このModuleは入力されたJSONデータ型のデータに値を追加する、という単純なものです。

CREATE OR REPLACE MLE MODULE plsql_mle_json_mod LANGUAGE JAVASCRIPT AS
 
function plsqlMLEJson(jsonStr) {
    jsonStr.value.data.push({"department_id": 1050, "department_name": "New Department 1050", "manager_id": 205, "location_id": 2600});
}
 
function plsqlMLEJson2(jsonStr) {
    jsonStr.data.push({"department_id": 1050, "department_name": "New Department 1050", "manager_id": 205, "location_id": 2600});
    return jsonStr;
}
 
export { plsqlMLEJson, plsqlMLEJson2 }
/

CREATE OR REPLACE PROCEDURE plsql_mle_json_proc(json_str IN OUT JSON)
AS MLE MODULE plsql_mle_json_mod
SIGNATURE 'plsqlMLEJson';
/

CREATE OR REPLACE FUNCTION plsql_mle_json_func(json_str JSON)
RETURN JSON
AS MLE MODULE plsql_mle_json_mod
SIGNATURE 'plsqlMLEJson2';
/

前者のfunction plsqlMLEJson はIN OUT型で渡された値にデータを追加しています。
後者の plsqlMLEJson2 は渡された値にデータを追加したものを、RETURNで戻り値として返します。

まずはIN OUT型を使うケースの動作確認です。

set serveroutput on
DECLARE
    l_json        JSON;
    l_json_obj    json_object_t := json_object_t();
    l_json_array  json_array_t := json_array_t();
BEGIN
    l_json_array.append(json_object_t('{ "department_id": 1010, "department_name": "New Department 1010", "manager_id": 200, "location_id": 1700 }'));
    l_json_obj.put('data', l_json_array);
    
    l_json := l_json_obj.to_json;
    dbms_output.put_line('json before: ' || json_serialize(l_json pretty));
    plsql_mle_json_proc(l_json);
    dbms_output.put_line('-------------');
    dbms_output.put_line('json after: ' || json_serialize(l_json pretty));
END;
/

json before: {
  "data" :
  [
    {
      "department_id" : 1010,


"department_name" : "New Department 1010",
      "manager_id" : 200,


"location_id" : 1700
    }
  ]
}
-------------
json after: {
  "data" :
  [
    {
      "department_id" : 1010,


"department_name" : "New Department 1010",
      "location_id" : 1700,


"manager_id" : 200
    },
    {
      "department_id" : 1050,


"department_name" : "New Department 1050",
      "manager_id" : 205,


"location_id" : 2600
    }
  ]
}

PL/SQLプロシージャが正常に完了しました。

json beforeの内容にデータが追加され、json afterとして標準出力されています。
この通り、PL/SQL内で作成したJSONデータ型のデータも、MLE JavaScript Moduleで問題なく扱えました。
ちなみにPL/SQLでJSONデータを扱う点について、上記サンプルをもとに補足します。
l_json_obj、l_json_arrayはPL/SQLでJSONを扱うために定義したオブジェクトです。
一方でl_jsonはOracle DatabaseのJSONデータ型のオブジェクトです。
MLE JavaScript ModuleにはJSONデータ型を引数として指定するので、下記処理によって変換した上でMLE Moduleに連携しています。

l_json := l_json_obj.to_json;

もう一方のRETURNを使ったケースもほぼ同じです。
相違点は戻り値を格納するためのJSONデータ型のオブジェクト l_json_res を追加で定義している部分になります。

set serveroutput on
DECLARE
    l_json        JSON;
    l_json_res    JSON;
    l_json_obj    json_object_t := json_object_t();
    l_json_array  json_array_t := json_array_t();
BEGIN
    l_json_array.append(json_object_t('{ "department_id": 1010, "department_name": "New Department 1010", "manager_id": 200, "location_id": 1700 }'));
    l_json_obj.put('data', l_json_array);
    
    l_json := l_json_obj.to_json;
    dbms_output.put_line('json before: ' || json_serialize(l_json pretty));
    l_json_res := plsql_mle_json_func(l_json);
    dbms_output.put_line('-------------');
    dbms_output.put_line('json after: ' || json_serialize(l_json_res pretty));
END;
/

json before: {
  "data" :
  [
    {
      "department_id" : 1010,


"department_name" : "New Department 1010",
      "manager_id" : 200,


"location_id" : 1700
    }
  ]
}
-------------
json after: {
  "data" :
  [
    {
      "department_id" : 1010,


"department_name" : "New Department 1010",
      "location_id" : 1700,


"manager_id" : 200
    },
    {
      "department_id" : 1050,


"department_name" : "New Department 1050",
      "manager_id" : 205,


"location_id" : 2600
    }
  ]
}

PL/SQLプロシージャが正常に完了しました。

MLE Moduleではなく動的MLEで実装したJavaScriptにPL/SQLからJSONデータを入力し、もとのPL/SQLに処理結果を返す

最後にMLE Moduleを使わず、PL/SQL内にインラインでJavaScriptを書く動的MLEにおけるJSONデータ型の扱いも検証しました。
ただ結論としては上手く動かない結果となりました。。
(2023/10/17 更新)正しく動作することを確認しましたので、誤った実装の直後に追記しました。
以下は誤った実装のバージョンです。

set serveroutput on
DECLARE 
    l_json        JSON;
    l_json_res    JSON;
    l_json_obj    json_object_t := json_object_t();
    l_json_array  json_array_t := json_array_t();
    l_ctx         dbms_mle.context_handle_t; 
    l_snippet     CLOB; 
BEGIN 
    l_json_array.append(json_object_t('{ "department_id": 1010, "department_name": "New Department 1010", "manager_id": 200, "location_id": 1700 }'));
    l_json_obj.put('data', l_json_array);
    
    l_json := l_json_obj.to_json;
    dbms_output.put_line('json before: ' || json_serialize(l_json pretty));
    
    l_ctx := dbms_mle.create_context(); 
    l_snippet := q'~ 
const bindings = await import('mle-js-bindings');
let jsonStr = bindings.importValue("jsonStr");
jsonStr.data.push({"department_id": 1050, "department_name": "New Department 1050", "manager_id": 205, "location_id": 2600});
bindings.exportValue("l_json_res", jsonStr);
~'; 
    dbms_mle.export_to_mle(l_ctx, 'jsonStr', l_json);
    dbms_mle.eval(l_ctx, 'JAVASCRIPT', l_snippet); 
    dbms_mle.import_from_mle(l_ctx, 'jsonStr', l_json_res);
    dbms_mle.drop_context(l_ctx); 
    dbms_output.put_line('-------------');
    dbms_output.put_line('json after: ' || json_serialize(l_json_res pretty));
EXCEPTION 
    WHEN OTHERS THEN 
        dbms_mle.drop_context(l_ctx); 
        RAISE; 
END; 
/

json before: {
  "data" :
  [
    {
      "department_id" : 1010,


"department_name" : "New Department 1010",
      "manager_id" : 200,


"location_id" : 1700
    }
  ]
}
DECLARE
*
行1でエラーが発生しました。:
ORA-04160: 解析エラーSyntaxError: <dyn-src-js>:2:23 Expected ; but found import const
bindings = await import('mle-js-bindings');
^
ORA-06512: 行34
ORA-06512: "SYS.DBMS_MLE", 行447
ORA-06512: 行26

以下は正常に動作したバージョンです。

SET SERVEROUTPUT ON
DECLARE 
    l_json        JSON;
    l_json_res    JSON;
    l_json_obj    json_object_t := json_object_t();
    l_json_array  json_array_t := json_array_t();
    l_ctx         dbms_mle.context_handle_t; 
    l_snippet     CLOB; 
BEGIN 
    l_json_array.append(json_object_t('{ "department_id": 1010, "department_name": "New Department 1010", "manager_id": 200, "location_id": 1700 }'));
    l_json_obj.put('data', l_json_array);
    
    l_json := l_json_obj.to_json;
    dbms_output.put_line('json before: ' || json_serialize(l_json pretty));
    
    l_ctx := dbms_mle.create_context(); 
    l_snippet := q'~ 
(async () => {
    const bindings = await import('mle-js-bindings');
    let jsonStr = bindings.importValue("jsonStr");
    jsonStr.data.push({"department_id": 1050, "department_name": "New Department 1050", "manager_id": 205, "location_id": 2600});
    bindings.exportValue("jsonStr", jsonStr);
})();
~'; 
    dbms_mle.export_to_mle(l_ctx, 'jsonStr', l_json);
    dbms_mle.eval(l_ctx, 'JAVASCRIPT', l_snippet); 
    dbms_mle.import_from_mle(l_ctx, 'jsonStr', l_json_res);
    dbms_mle.drop_context(l_ctx); 
    dbms_output.put_line('-------------');
    dbms_output.put_line('json after: ' || json_serialize(l_json_res pretty));
EXCEPTION 
    WHEN OTHERS THEN 
        dbms_mle.drop_context(l_ctx); 
        RAISE; 
END; 
/

json before: {
  "data" :
  [
    {
      "department_id" : 1010,

"department_name" : "New Department 1010",
      "manager_id" : 200,

"location_id" : 1700
    }
  ]
}
-------------
json after: {
  "data" :
  [
    {
      "department_id" : 1010,

"department_name" : "New Department 1010",
      "location_id" : 1700,

"manager_id" : 200
    },
    {
      "department_id" : 1050,

"department_name" : "New Department 1050",
      "manager_id" : 205,

"location_id" : 2600
    }
  ]
}

以前は ORA-04160 が発生し正常動作を確認できませんでしたが、qクォート内の書き方を修正したところ正しく動作しました。
なおPL/SQLから動的MLEへのデータ連携には mle-js-bindings というModuleに実装されている importValue、exportValue という関数を使う必要があります。
そのため mle-js-bindings を始めにimportします。
動的MLEでModuleをimportする方法は以下の公式ドキュメントにまとめられています。
https://oracle-samples.github.io/mle-modules/docs/mle-js-bindings/23ai/#exchanging-values-between-plsql-and-javascript