日日是Oracle APEX: OML4R

ラベル OML4R の投稿を表示しています。すべての投稿を表示

2025年7月17日木曜日

Autonomous DatabaseでRで記述したファンクションを表関数として実行する

Autonomous DatabaseでOML4RのSQL APIを呼び出し、Rで記述したファンクションを表関数として実行してみます。OML4Pyで同様の作業を実施し、「Autonomous DatabaseでPythonで記述したファンクションを表関数として実行する」として記事を公開しています。

PythonではpyqEval、pyqTableEval、pyqRowEval、pyqGroupEval、pyqIndexEvalとして提供されているSQL APIが、R向けにはrqEval2、rqTableEval2、rqRowEval2、rqGroupEval2、rqIndexEval2として提供されています。SQL APIが呼び出す言語はPythonとRで異なりますが、SQL APIの役割や引数は同じです。

OML4PyのSQL APIの場合、オンプレミスとAutonomous DatabaseでSQL APIのファンクション名は同じだったのですが、OML4RはオンプレミスではrqEval、rqTableEval、rqRowEval、rqGroupEvalとなっています。Autonomous DatabaseのSQL APIはこれらのファンクション名の末尾に2が付いていて、異なるファンクションになっています。今回はAutonomous Databaseで作業を行うので、末尾に２がついたSQL APIのファンクションを呼び出します。

本記事では、すでに作成済みのAPEXアプリケーションを使ってOML4RのSQL APIをAutonomous Databaseから呼び出してみます。対象となるRスクリプトは、OML4R User’s Guideの以下のセクションに記載されています。

9.6 SQL API for Embedded R Execution with Autonomous Database

アプリケーションの実装はOML4Pyで作成したものと同じく、以下の方針でSQL APIを呼び出します。

SQL APIを呼び出す処理は開発者が実施し、表関数の結果を実表にコピーします。
APEXアプリケーションでは実表を扱い、SQL APIを直接呼び出すことはしません。

作成済みのAPEXアプリケーションは以下よりダウンドードできます。

https://github.com/ujnak/apexapps/blob/master/exports/oml4r-sqlapi-admin.zip

APEXからみるとAutonomous DatabaseでのSQL APIは、OML4PyとOML4Rでは決定的に異なる部分があります。

Autonomous DatabaseでのAPEXのSQLコマンドで以下のSELECT文を実行し、セッションのPROXY_USER、SESSIOON_USERおよびCURRENT_USERを確認します。

select
sys_context('userenv','proxy_user')   as proxy_user,
sys_context('userenv','session_user') as session_user,
sys_context('userenv','current_user') as current_user
from dual

Autonomous Databaseでは、PROXY_USERはORDS_PUBLIC_USER、SESSION_USERはORDS_PLSQL_GATEWAY、CURRENT_USERはAPEXワークスペースのパーシング・スキーマになります。

OML4Rが提供しているビューUSER_RQ_SCRIPTSをAPEXアプリケーションから検索するときは、APEXワークスペースのパーシング・スキーマではなくSESSION_USERのORDS_PLSQL_GATEWAYを所有者として認識しています。また、rqScriptCreateやrqScriptDropのファンクションも、ユーザーORDS_PLSQL_GATEWAYで処理されます。結果として作成したスクリプトの所有者はORDS_PLSQL_GATEWAYになります。OML4RのSQL APIはAPEXワークスペースのパーシング・スキーマで実行されます。そのため、Rスクリプトの所有者が異なり、APEXアプリケーションから呼び出されたSQL APIは、指定されたRスクリプトを見つけることができません。

APEXアプリケーションではプライベートなRスクリプトを管理できません。そのため、今回はすべてのRスクリプトをグローバルなスクリプトとして作成しています。所有者を限定したい場合は、OMLノートブックなどからスクリプトの作成や削除を行う必要があります。

これから実施する作業は、OML4Pyで行った作業とほとんど同じですが、スクリプト名を読み替えたりしなくても良いように省略せずに記載します。

事前準備

前提条件ですが、Autonomous DatabaseではAPEXのワークスペースとしてAPEXDEVが作成済みで、スキーマとしてWKSP_APEXDEVがデフォルト・パーシング・スキーマとして割り当てられている状態とします。異なる名前のスキーマを使用する場合は、以下の説明に含まれるWKSP_APEXDEVの部分を置き換えてください。

OML4RのSQL APIを呼び出すために必要な権限を、スキーマWKSP_APEXDEVに与えます。

SQL Developer Webに管理者ユーザーADMINでサインインし、管理のデータベース・ユーザーを開きます。

スキーマWKSP_APEXDEVの編集画面を開き、OMLをオンにします。また、認証トークンの取得に、スキーマのパスワードが必要です。APEXワークスペースと同時に作成されたスキーマにはパスワードが設定されていないので、未設定であればパスワードも設定します。

スキーマWKSP_APEXDEVにネットワークACLを追加します。

SQLワークシートを開きます。

9.6.1 Access and Authorization Procedures and Functionsの手順に沿って作業を進めます。

対象となるホスト（ドメイン）として、adb.<リージョン名>.oraclecloudapps.comを与えます。現在アクセスしているURLから、リージョン名を見つけることができます。

rqAppendHostAceを呼び出して、スキーマWKSP_APEXDEVにネットワークACLを追加します。（OML4PyではpyqAppendHostAceです。接頭辞のrqとpyqが異なります）。

begin
    rqAppendHostAce('WKSP_APEXDEV','adb.us-ashburn-1.oraclecloudapps.com');
end;

rqGetHostAceを呼び出して設定を確認します。

DECLARE 
    hostname VARCHAR2(4000);
BEGIN
    hostname := rqGetHostACE('WKSP_APEXDEV'); 
    DBMS_OUTPUT.put_line ('hostname: ' || hostname);
END;

rqAppendHostAceにはドメイン名を渡していますが、rqGetHostAceではAutonomous Databaseの接続先であるホスト名が返されます。

このホスト名は、アクセス・トークンを取得するURLのホスト名になります。

OML4Pyのサンプル・スクリプトを実行する際に使用する環境を作成します。

OMLノートブックにサインインします。

https://<テナントID>-<データベース名>.adb.us-ashburn-1.oraclecloudapps.com/oml

ユーザー名とパスワードとして、管理者ユーザーADMINとそのパスワードを指定します。

サインインをクリックします。

condaを使ってRを実行する環境を作成します。

スクラッチパッドを開きます。

3.2 Administrative Tasks for Creating and Saving a Conda Environmentに記載されているコマンドを記述します。

１番目の段落に以下を記述します。Rを実行する環境としてmyrenvを作成します。

%conda

create -n myrenv -c conda-forge --override-channels --strict-channel-priority r-base=4.0.5 r-forecast r-ggplot2

２番目の段落に以下を記述します。作成した環境myrenvをオブジェクト・ストレージにアップロードします。

%conda

upload myrenv --overwrite --description 'Install R forecast and ggplot2 packages' -t user 'WKSP_APEXDEV' -t application 'OML4R'

３番目の段落に以下を記述します。保存されている環境を一覧します。

%conda

list-saved-envs

４番目の段落に以下を記述します。環境myrenvの情報を表示します。

%conda

list-saved-envs -e myrenv

以上でスクラッチパッドを実行します。

Rの実行環境としてmyrenvが作成され、その後にオブジェクト・ストレージにアップロードされました。

これで、環境myrenvをSQL APIの引数env_nameに指定できるようになりました。SQL APIの引数env_nameにmyrenvを指定したときは、SQL APIの引数scr_nameで指定されたスクリプト内で、環境myrenvにロードされているパッケージを呼び出すことができます。

OMLノートブックからサインアウトし、ユーザーWKSP_APEXDEV（APEXのワークスペース・スキーマ）でサインインし直します。

例題の実行に使用する表IRISを作成します。OML4Pyでの作業で作成した表IRISとは定義や内容が異なるため、すでに表がある場合は削除します。

ADMINでの作業と同様に、スクラッチパッドを開きます。

１番目の段落に以下を記述します。

%sql
drop table if exists iris

２番目の段落に以下を記述します。

%r
ore.create(iris, table = 'IRIS')

３番目の段落に以下を記述します。

%sql
select count(*) from iris

以上でスクラッチパッドを実行します。

３番目の段落の結果として、表IRISに含まれるデータの件数が返されていることを確認します。

以上で準備は完了です。

APEXアプリケーションのインポート

APEXのワークスペースにサインインします。

OML4RのSQL APIを呼び出すAPEXアプリケーションをインポートします。

https://github.com/ujnak/apexapps/blob/master/exports/oml4r-sqlapi-admin.zip

ダウンロードしたファイルoml4r-sqlapi-admin.zipを選択します。

次へ進みます。

アプリケーションのインストールをクリックします。

サポートするオブジェクトのインストールをクリックします。サポートするオブジェクトのインストールとして、SQL APIから呼び出すRスクリプトの作成と、SQL APIの実行結果を保存する表の作成が行われます。

APEXアプリケーションがインポートされました。

アプリケーションを実行します。

APEXアプリケーションにサインインすると、以下のホーム・ページが開きます。

以上でAPEXアプリケーションのインポートが完了しました。

OML4R SQL APIの実行

OML4Rのサンプル・コードを実行する前に、認証トークンの取得とセットを行います。

スキーマのパスワードを入力し、ボタンGet Token / Set Auth Tokenをクリックします。

ボタンをクリックすると以下のコードが実行され、認証トークンの取得と設定が行われます。

ページが送信されたのち、Is Token SetがTRUEに変われば、正常に認証が完了しています。

認証トークンの有効期限は3600秒なので、Is Token SetがTRUEでも有効期間が過ぎるとSQL APIの呼び出しが失敗するようになります。その場合はパスワードを入力し、再認証します。

（REST API for Embedded R ExecutionのAuthenticateのセクションはOML4Pyと違い、リフレッシュ・トークンについての記述自体がありません。）

最初にRファンクションRandomRedDot2を、SQL APIのrqEval2から呼び出してみます。

ナビゲーション・メニューのR Scriptsを開きます。OML4RのUser's Guideに記載されているSQL APIから呼び出すRスクリプトは、アプリケーションのインポート時に作成しています。

Rスクリプトの編集ページにボタンFormatを作成しています。Rのコード・フォーマッターはRで記述されているので、ページにWebRとstylerをロードしてRのフォーマッターを実行しています。

以下の内容を静的アプリケーション・ファイルwebr-styler.jsとして作成します。

ページ・プロパティのJavaScriptのファイルURLに以下を記述しています。

[module]#APP_FILES#webr-styler#MIN#.js

ページ・ロード時に実行に以下を記述します。

initializeWebR();

ボタンFormatをクリックしたときに、動的アクションでJavaScriptファンクションformatCode()を呼び出しています。

Rスクリプトの作成、編集、削除ができます。ただし、OML4PyのPythonスクリプトの所有者はAPEXのワークスペース・スキーマでした。OML4Rではグローバル・スコープで作成され、所有者はRQSYSになります。

ボタン作成または変更の適用をクリックしたときに、rqScriptCreateを呼び出してRスクリプトを作成しています。引数v_globalにtrueを与えています。

declare
    l_script clob;
begin
    l_script := :P3_SCRIPT;
    l_script := replace(:P3_SCRIPT, CHR(13)||CHR(10),CHR(10)); -- remove CR
    sys.rqScriptCreate(
        v_name      => :P3_NAME,
        v_script    => l_script,
        v_global    => true,
        v_overwrite => ( :REQUEST = 'SAVE' )
    );
end;

ボタン削除をクリックしたときに、rqScriptDropを呼び出してRスクリプトを削除しています。こちらでも引数v_globalにtrueを渡しています。

begin
    sys.rqScriptDrop(
        v_name   => :P3_NAME,
        v_global => true,
        v_silent => false
    );
end;

ボタン9-32: RandomRedDot2 - JSONをクリックすると、以下のコードが実行されます。SQL APIの結果を保存する表RQ_E01の内容を削除したのち、SQL APIの検索結果を表RQ_E01にコピーしています。SQL APIの引数OUT_FMTの指定に依存して、コピー対象に含まれる列は変わりますが、他の同期呼び出しの処理もほとんど同じです。

begin
    delete from rq_e01;
    /* copy SQL API result into the table */
    insert into rq_e01(name, value)
    select name, value from table(
        rqEval2(
            par_lst => '{"ore_service_level":"LOW"}',
            out_fmt => 'JSON',
            scr_name => 'RandomRedDots2'
        )
    );
end;

SQL APIの実行結果を実表にコピーしたのち、ブランチによって、その表をソースとした対話モード・レポートのページへリダイレクトします。

結果としてボタン9-32: RandomRedDot2 - JSONをクリックすると、以下のようにSQL APIの実行結果を表示する対話モード・レポートのページが開きます。

後は上から順番にSQL APIからRスクリプトを呼び出していきます。

RandomRedDot2 - PNG、test_ggplot2_noinp - PNGといった末尾にPNGが付いている処理は、チャートを出力します。

SQL APIの非同期ジョブの呼び出し方法を説明します。

最初に以下のコードで非同期ジョブをサブミットします。SQL APIの引数PAR_LSTに与えるJSONに"ore_asyc_flag": trueを含めます。SQL APIの結果の列VALUEにJOB IDを含んだURLが返されるので、URLからJOB IDを取り出しています。

非同期ジョブの開始時に得られたJOB IDより、ジョブのステータスを確認します。以下のコードを実行します。

非同期ジョブが実行中の場合は、列VALUEにjob is still runningが返されます。

非同期ジョブが終了したら、以下のコードを実行して結果を実表にコピーします。

最後に実表の対話モード・レポートのページにリダイレクトし、処理結果のレポートを表示します。

以上でOML4RのUser's Guideに記載されている、Autonomous Database向けのSQL APIのサンプルはすべて実行できました。

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

Oracle APEXのアプリケーションからRのスクリプトを呼び出す際の参考になれば幸いです。

完

2025年7月8日火曜日

Oracle DatabaseでPythonで記述したファンクションを表関数として実行する

Oracle Machine Learning for Python(OML4Py)をOracle Databaseのサーバーに組み込むことにより、Pythonで記述したファンクションを表関数として実行できます。この機能はSQL APIと呼ばれています。

Oracle Machine Learning for Python, Relase 2-23ai

User's Guide

13.6 SQL API for Embedded Python Execution with On-premises Database

Oracle Machine Learning for R(OML4R)も同様が提供されています。

Oracle Machine Learning for R, Release 2.0-23ai

User's Guide

9.5 SQL API for Embedded R Execution with On-premises Database

表関数となるPythonまたはRのファンクションの作成、更新、削除および作成したファンクションを、SQLから表関数として呼び出して動作を確認するAPEXアプリケーションを作成してみます。最近はRよりもPythonの方が人気のある言語なので、本記事ではPythonでの実装について紹介します。

OML4Pyのアーキテクチャはx86-64限定です。実行できる環境を作るだけでも大変なので、オラクルが提供しているOracle Database 23ai FreeとOracle REST Data Servicesのコンテナ・イメージからAPEXの環境を作るスクリプトを更新しました。

APEX環境の作成

今となっては珍しいIntel CPUの入ったMacbook Proで、podmanを使って環境を作成します。

GitHubからスクリプトをクローンします。

git clone https://github.com/ujnak/apex-podman-setup.git

% git clone https://github.com/ujnak/apex-podman-setup.git

Cloning into 'apex-podman-setup'...

remote: Enumerating objects: 363, done.

remote: Counting objects: 100% (15/15), done.

remote: Compressing objects: 100% (15/15), done.

remote: Total 363 (delta 6), reused 0 (delta 0), pack-reused 348 (from 2)

Receiving objects: 100% (363/363), 103.60 KiB | 639.00 KiB/s, done.

Resolving deltas: 100% (200/200), done.

作成されたディレクトリapex-podman-setupに移動し、APEX環境の構成スクリプトconfig_apex.shを実行します。第１引数がデータベースのSYSのパスワード、第２引数がAPEXの管理者パスワードになります。

cd apex-podman-setup
apex-podman-setup % sh config_apex.sh <SYSのパスワード> <APEX管理者パスワード>

ネットワークのポート番号として1521(SQL*Net)、8181(HTTP)、8443(HTTPS)、27017(MongoDB)を使用するため、ホスト上で未使用になっている必要があります。使用済みの場合はapex.yaml.templateに記載されているhostPortを変更します。

2019年のMacbook Proで、スクリプトの終了まで20分程度の時間がかかりました。スクリプトが終了すれば、APEXの環境は完成しています。

以下のURLよりAPEXとORDSにアクセスできます。

http://localhost:8181/ords/

% cd apex-podman-setup

apex-podman-setup % sh config_apex.sh <SYSPassword> <APEXPassword>

Trying to pull container-registry.oracle.com/database/free:latest...

Getting image source signatures

Copying blob sha256:3c19df83dc536c431f1f991013d53f88157d03afed4452c014b3a0deb32add62

Copying blob sha256:769e9048b21fc9ec5c1af97cb44cf1298993838fee496c6a6cf471cbd9656e0c

Copying blob sha256:7ce287ddcf023475b16b98c11a145816110e4870844f6008eb11fc12cb98bc13

Copying blob sha256:fb5a2405efd168f49f8b9ef5d65df63abcc4e71805762287ea271ca04152c917

Copying blob sha256:60b151ee286ca7b9cf8876c091384ce0e3f46f723f383b74d573f5f3c8a83181

Copying blob sha256:67273d20686cb6adc472d07c54f5b2ecacab066d73d41b419e64be5141d9c063

Copying blob sha256:18c61db4fa1af28adfb01c770f0c8d8e367ca55026f565bbb2f604fb983b223d

[中略]

ORDS: Release 25.2 Production on Fri Jul 04 07:19:48 2025

Configuration:

/etc/ords/config

The global setting named: standalone.static.path was set to: /opt/oracle/apex/images

apex

ynakakoshi@nsmacbookintel apex-podman-setup %

APEXをインストールしたOracle Database 23ai Freeに、OML4Pyを構成します。構成に必要なファイルを配置するディレクトリomlを作成します。

mkdir oml

apex-podman-setup % mkdir oml

apex-podman-setup %

ディレクトリoml以下にOML4Py 2.1クライアントのZIPファイルとPython-3.12.6のソース・コードを配置します。

OML4Py 2.1クライアントV1048628-01.zipは以下のページよりダウンロードします。
https://docs.oracle.com/en/database/oracle/machine-learning/oml4py/2-23ai/mlpug/install-oml4py-client-linux-premises-databases.html

Python-3.12.6.tgzは以下のリンクよりダウンロードします。

https://www.python.org/ftp/python/3.12.6/Python-3.12.6.tgz

ダウンロードしたファイルをディレクトリoml以下に配置します。

ls oml

apex-podman-setup % ls oml/

Python-3.12.6.tgz V1048628-01.zip

apex-podman-setup %

OML4Pyを構成するスクリプトconfig_oml4py.shを、データベースのコンテナapex-dbで実行します。

podman exec apex-db ./work/config_oml4py.sh

OSの更新にあたってネットワーク経由で沢山のPRMをダウンロードします。そのため、ネットワークの速度にも依存して時間がかかります。Pythonのコンパイルやパッケージのインストール、データベースの構成もあり、全体で40分くらいは時間がかかりました。

スクリプトが正常終了すれば、OML4Py 2.1のサーバーとクライアントの両方の構成が完了しています。

apex-podman-setup % podman exec apex-db sh ./work/config_oml4py.sh

Oracle Linux 8 BaseOS Latest (x86_64) 4.6 MB/s | 102 MB 00:21

Oracle Linux 8 Application Stream (x86_64) 3.5 MB/s | 72 MB 00:20

Oracle Linux 8 Development Packages (x86_64) 3.6 MB/s | 216 MB 01:00

Last metadata expiration check: 0:00:59 ago on Fri Jul 4 07:37:54 2025.

Package openssl-1:1.1.1k-14.el8_6.x86_64 is already installed.

Dependencies resolved.

=======================================================================================================

Package Arch Version Repository Size

=======================================================================================================

Installing:

bzip2-devel x86_64 1.0.6-28.el8_10 ol8_baseos_latest 224 k

[中略]

Oracle Machine Learning for Python 2.1 Client.

Checking platform .................. Pass

Checking Python .................... Pass

Checking dependencies .............. Pass

Checking OML4P version ............. Pass

Current configuration

Python Version ................... 3.12.6

PYTHONHOME ....................... /home/oracle/python

Existing OML4P module version .... 2.1

Operation ........................ Install/Upgrade

Found existing installation: oml 2.1

Uninstalling oml-2.1:

Successfully uninstalled oml-2.1

Processing ./client/oml-2.1-cp312-cp312-linux_x86_64.whl

Installing collected packages: oml

Successfully installed oml-2.1

Done

apex-podman-setup %

OML4Rの環境を構成します。以下のリンク先よりOML4Rのサポート・パッケージを含むアーカイブoml4r-supporting-linux-x86-64-2.0.zipをダウンロードし、ディレクトリomlの下に配置します。
https://www.oracle.com/database/technologies/oml4r-downloads.html

ディレクトリoml以下に、これらのファイルが配置されている状態です。

ls oml

apex-podman-setup % ls oml

oml4r-supporting-linux-x86-64-2.0.zip V1048628-01.zip

Python-3.12.6.tgz

apex-podman-setup %

OML4Pyと同様に、OML4Rの構成スクリプトconfig_oml4r.shを、データベースのコンテナで実行します。

podman exec apex-db ./work/config_oml4r.sh

OML4Rは10分程度と、若干早く構成が完了しました。スクリプトが正常終了すると、OML4Rの構成が完了しています。

apex-podman-setup % podman exec apex-db sh ./work/config_oml4r.sh

Oracle Linux 8 BaseOS Latest (x86_64) 37 kB/s | 4.3 kB 00:00

Oracle Linux 8 Application Stream (x86_64) 93 kB/s | 4.5 kB 00:00

Oracle Linux 8 CodeReady Builder (x86_64) - Uns 5.6 MB/s | 13 MB 00:02

Oracle Linux 8 Addons (x86_64) 5.4 MB/s | 39 MB 00:07

Dependencies resolved.

======================================================================================================================

Package Arch Version Repository Size

======================================================================================================================

Installing:

R x86_64 4.0.5-1.0.1.el8 ol8_addons 21 k

cairo-devel x86_64 1.15.12-6.el8 ol8_appstream 273 k

Installing dependencies:

R-core x86_64 4.0.5-1.0.1.el8 ol8_addons 59 M

R-devel x86_64 4.0.5-1.0.1.el8 ol8_addons 102 k

adobe-mappings-cmap noarch 20171205-3.el8 ol8_appstream 2.1 M

adobe-mappings-cmap-deprecated noarch 20171205-3.el8 ol8_appstream 118 k

[中略]

* installing *binary* package 'statmod' ...

* DONE (statmod)

* installing *binary* package 'tibble' ...

* DONE (tibble)

* installing *binary* package 'tidyselect' ...

* DONE (tidyselect)

* installing *binary* package 'utf8' ...

* DONE (utf8)

* installing *binary* package 'vctrs' ...

* DONE (vctrs)

Making 'packages.html' ... done

apex-podman-setup %

以上で、OML4PyとOML4Rの構成が完了しました。

APEXのワークスペースを作成します。

ホストからSQLclを使って、コンテナapex-dbで動作しているデータベースにSYSで接続します。

sql sys/[SYSのパスワード]@localhost/freepdb1 as sysdba

apex-podman-setup % sql sys/[SYSのパスワード]@localhost/freepdb1 as sysdba

SQLcl: 金 7月 04 17:29:00 2025のリリース25.1 Production

接続先:

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

Version 23.8.0.25.04

SQL>

APEXのワークスペースとそれに紐づくスキーマおよびワークスペースの管理者ユーザーを作成します。

@create_workspace <ワークスペース名> <管理者ユーザー名> <管理者パスワード> <管理者メールアドレス>

以下ではAPEXのワークスペース名をAPEXDEV、管理者ユーザーをADMINと指定しています。ワークペースに紐づくスキーマは、ワークスペース名に接頭辞としてWKSP_を付けるAutonomous Databaseの仕様に合わせて、WKSP_APEXDEVとして作成されます。

SQL> @create_workspace APEXDEV ADMIN <パスワード> <メールアドレス>

SQL>

SQL> define WKSPNAME = &1

SQL> define ADMINNAME = &2

SQL> define ADMINPASS = &3

SQL> define ADMINMAIL = &4

SQL>

SQL> -- create default parsing shema for worksapce.

SQL> @create_schema WKSP_&WKSPNAME

SQL> set echo on

SQL>

SQL> define SCHEMA = &1

SQL>

SQL> -- create database user for apex parsing sdchema on Oracle Database Free

SQL> create user if not exists &SCHEMA default tablespace users temporary tablespace temp quota unlimited on users;

旧:create user if not exists &SCHEMA default tablespace users temporary tablespace temp quota unlimited on users

新:create user if not exists WKSP_APEXDEV default tablespace users temporary tablespace temp quota unlimited on users

User WKSP_APEXDEVは作成されました。

[中略]

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

SQL>

SQL> commit;

コミットが完了しました。

SQL> exit

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

Version 23.8.0.25.04から切断されました

apex-podman-setup %

以上でAPEXのワークスペースとそれに紐づくスキーマが作成されました。再度データベースにSYSで接続し、スキーマにOML4PyとOML4Rの処理に必要な権限とロールを与えます。

grant create mining model to <スキーマ名>;
grant db_developer_role, pyqadmin, rqadmin to <スキーマ名>;
grant execute on ctx_ddl to <スキーマ名>;

apex-podman-setup % sql sys/********@localhost/freepdb1 as sysdba

SQLcl: 金 7月 04 17:36:58 2025のリリース25.1 Production

接続先:

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

Version 23.8.0.25.04

SQL> grant create mining model to wksp_apexdev;

Grantが正常に実行されました。

SQL> grant db_developer_role, pyqadmin, rqadmin to wksp_apexdev;

Grantが正常に実行されました。

SQL> grant execute on ctx_ddl to wksp_apexdev;

Grantが正常に実行されました。

SQL> exit

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

Version 23.8.0.25.04から切断されました

ynakakoshi@nsmacbookintel apex-podman-setup %

以上で準備は完了です。

作成したワークスペースにサインインし、APEXアプリケーションの開発を始めます。

APEXアプリケーションの作成

アプリケーションの作成を開きます。アプリケーションの名前はEmbedded Script Executionとします。そのままアプリケーションの作成を実行し、空のアプリケーションを作成します。

表関数として実行されるPythonスクリプトの一覧および作成、更新、削除を行うフォームの画面を作成します。

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

スクリプトの一覧はクラシック・レポートで表示します。

クラシック・レポートを選択します。

レポートのページはページ番号が２、名前をPython Scriptsとします。ページ・モードは標準です。フォーム・ページを含めるをオンにし、ページ番号が３、フォーム・ページ名をEdit Python Scriptとします。フォーム・ページ・モードに標準を選択します。

一般にレポートとフォームの組み合わせでは、フォーム・ページ・モードはモーダル・ダイアログかドロワーを選択します。今回はスクリプトの編集画面になるので、意図せずにウィンドウが閉じて編集したスクリプトが破棄されることが無いように、フォーム・ページ・モードとして標準を選択します。

フォーム・ページ・モードとして標準を選ぶと、ページ作成後にページ・モードをモーダル・ダイアログやドロワーに変更するのは大変手間がかかります。その逆、モーダル・ページやドロワーを標準に変更するのも大変です。大抵はページを削除して、ページ作成ウィザードを呼び出して作り直した方が早くて確実です。

データ・ソースのソース・タイプにSQL問合せを選択し、SQL問合せとして以下を記述します。ビューUSER_PYQ_SCRIPTSから、表関数向けに作成されたPythonスクリプトを一覧できます。このビューに対するINSERT、UPDATE、DELETE操作はできませんが、APEXのページ作成ウィザードは通常の表と同様にフォームのページを作成します。

select name id, name, script from user_pyq_scripts

以下のスクリーンショットには含まれていませんが、ナビゲーションのブレッドクラムの作成とナビゲーションの作成は、デフォルトのオンです。

以上の設定で次へ進みます。

主キー列1にID (Varchar2)を選択します。列IDは列NAMEの別名ですが、主キー列に設定するとレポート上はフォームへのリンクかまたは非表示列になります。レポート上に列NAMEを通常の列としても扱いたいので、列IDとNAMEの２つの列を作成しています。

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

レポートとフォームのページが作成されます。

スクリプトを作成しないとレポートの表示を確認できないため、最初にフォームから編集します。

ページ・デザイナでページ番号３のフォームのページを開きます。

左ペインでプロセス・ビューを開きます。

ウィザードが作成したプロセスプロセス・フォームEdit Python Scriptを削除します。

代わりにPythonスクリプトの作成および更新するプロセスScriptCreateを作成します。

実行するPL/SQLコードは以下です。

declare
    l_script clob;
begin
    l_script := :P3_SCRIPT;
    l_script := replace(:P3_SCRIPT, CHR(13)||CHR(10),CHR(10)); -- remove CR
    sys.pyqScriptCreate(
        v_name      => :P3_NAME,
        v_script    => l_script,
        v_global    => false,
        v_overwrite => ( :REQUEST = 'SAVE' )
    );
end;

Pythonスクリプトは改行がCRLFとLFの両方を受け付けるようですが、RスクリプトはCRLFだとエラーになります。そのため、Rに合わせてスクリプト中のCRLFをLFに置き換えています。

押されたボタンが変更の適用（REQUESTとしてはSAVE）のときは、p_overwriteにtrueを渡し、スクリプトの更新を許可しています。

サーバー側の条件は、ボタン作成および変更の適用のクリックで実行されるように、タイプにリクエストは値に含まれるを選択し、値にCREATE SAVEを設定します。

Pythonスクリプトの削除を行なうプロセスScriptDropを作成します。

実行するPL/SQLコードは以下です。

begin
    sys.pyqScriptDrop(
        v_name   => :P3_NAME,
        v_global => false,
        v_silent => false
    );
end;

サーバー側の条件のボタン押下時にDELETEを選択します。

以上で、変更を保存します。

これで、Pythonスクリプトの基本的な管理画面が出来上がりました。

ページ・デザイナでレポートのページを開きます。

列SCRIPTにPythonスクリプトが表示されます。改行が正しく表示されるように、列の書式のHTML式に以下を記述します。

<pre>#SCRIPT#</pre>

レポートの属性を開き、パフォーマンスの遅延ロードをオンにします。ビューUSER_PYQ_SC RIPTSおよびUSER_RQ_SCRIPTSを参照するにあたって必須の設定になります。

これらのビューは実表でないためか、ページ・ロード時の検索では行を返さない（０行になる）ようです。

アプリケーションを実行してPythonスクリプトを登録してみます。

ドキュメントの以下のセクションで紹介されているpyqFun1を登録してみます。文字列Hello World from lambda!を返す簡単なファンクションです。

13.6.2 pyqEval Function (On-Premises Database)

Name: pyqFun1
Script:
func = lambda: "Hello World from a lambda!"

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

フォームにNameとScriptを入力し、作成をクリックします。

レポートのページに戻ります。

SQLに埋め込み可能なPythonスクリプトとして、pyqFun1が作成されたことが確認できます。

SQLコマンドから、ドキュメントに記載されている以下のSELECT文を実行します。

SELECT name, value 
  FROM table(pyqEval(
             NULL,
             'XML',
             'pyqFun1'));

SQL APIのpyqEvalから、Pythonで記述したファンクションpyqFun1が呼び出されます。

結果として文字列Hello World from a lambda!がXML形式で出力されます。

表関数として実行できるPythonのSQL APIには、pyqEval、pyqTableEval、pyqRowEval、pyqGroupEvalがあります。これらのSQL APIは、引数INP_NAM、PAR_LST、OUT_FMT、GRP_COL、SCR_NAME、SCR_OWNERのうち、いくつかまたは全てを受け付けます。

実行するSQL APIのタイプを選択し、それぞれの引数に値を入力してSQL APIを呼び出すページを作成します。

ページの作成を実行し、空白ページを選択します。

ページの名前はPython Evalとします。

ページの作成を実行します。

作成された空白ページに、実行するSQL APIを選択するページ・アイテムとしてP4_TYPE、SQL APIの引数として与える値を設定するページ・アイテムとしてP4_SCR_NAME、P4_INP_NAM、P4_PAR_LST、P4_OUT_FMT、P4_GRP_COL、P4_ROW_NUMを作成します。

ボタンEVALをクリックするとページの送信を実行します。ただし、ボタンEVALに紐づいて実行されるプロセスは存在しません。SQL APIを呼び出すSELECT文は、レポートのソースで組み立てます。

クラシック・レポートResultのソースのタイプにSQL問合せを返すファンクション本体を選択し、SQL問合せを戻すPL/SQLファンクション本体として以下を記述します。

レポートの列は引数OUT_FMTの値で決まります。レポート列が動的に変更されるため、汎用列名の使用をオンにしています。

SQL APIの種類が固定されていれば、SELECT文を動的に生成する必要はなく、また、引数OUT_FMTの値が固定されていれば、レポート列もデザイン時に決まります。

SQL APIの呼び出しについても、レポートの属性の遅延ロードをオンにする必要があります。

以上でSQL APIを呼び出すAPEXアプリケーションは完成です。

ドキュメントに記載されている例を実行してみます。

最初にExample 13-17 Using the pyqEval Functionを実行します。PythonスクリプトpyqFun1はすでに作成済みです。

pyqFun1を実行します。OUT_FMTにXMLが指定されているため、レポートにXMLが表示されます。

pyqFun2を作成します。このファンクションは、さまざまなデータ型を持つNumPyの構造化配列を生成して返します。

pyqFun2を実行します。OUT_FMTにJSON形式で出力列が指定されていますが、汎用列での列名付けを実装していないため、レポートの列名はCol01、Col02となっています。

Example 13-18 Using the pyqTableEval Functionの例を実行します。

Pythonスクリプトcreate_iris_tableを作成します。このファンクションはscikit-learnのIrisデータを読み込み、それをPandasのDataFrame形式で表形式に変換して返します。

このPythonスクリプトは、表IRISの作成に使用します。

SQLコマンドでCREATE TABLE文を実行します。

PythonスクリプトmyLinearRegressionModelを作成します。このファンクションは、scikit-learnの線形回帰モデルを訓練し、そのモデルをOMLのデータストアに保存します。

myLinearRegressionModelを実行します。引数datにはINP_NAMに与えた表IRISのデータが渡されます。引数modelNameおよびdatastoreNameには、PAR_LSTにJSONで与えているlinregr、pymodelが渡されます。

OMLデータストアの内容を確認します。

select * from all_pyq_datastore_contents

OMLデータストアpymodelに、オブジェクトとしてモデルlinregrが保存されていることが確認できます。

Example 13-19 Using the pyqRowEval Functionの例を実行します。

Pythonスクリプトsample_iris_tableを作成します。このファンクションは、Irisデータセットを読み込んで整形し、指定した数だけランダムにサンプリングして返します。

このPythonスクリプトは、表SAMPLE_IRISの作成に使用します。

SQLコマンドでCREATE TABLE文を実行します。sizeに20を指定しているため、表SAMPLE_IRISには20行挿入されます。

PyhthonスクリプトlinregrPredictを作成します。

OMLデータストアに保存された回帰モデル（先ほど保存したlinregr）を読み込んで、新しいデータ（表SAMPLE_IRIS）に対して予測を行い、その結果を元のデータに付加して返します。

linregrPredictを実行します。INP_NAMは表SAMPLE_IRIS、PAR_LSTに与えるJSONでモデル名のlinregrを指定します。

レポートに列Col06（OUT_FMTで設定されている列名はPred_Petal_Width - 花びらの横幅）が追加されて表示されます。

Example 13-20 Using the pyqGroupEval Functionの例を実行します。

Pythonスクリプトcreate_iris_tableおよび表IRISは作成済みです。

Pythonスクリプトmygroupcountを作成します。与えられたデータを、列Speciesの値ごとに件数をカウントして返します。

mygroupcountを実行します。

OML4Pyのドキュメントに記載されているSQL APIの呼び出し例は、以上ですべて実行できました。

今回作成したアプリケーションのエクスポートを以下に置きました。

https://github.com/ujnak/apexapps/blob/master/exports/oml-embedded-script-execution.zip

本記事では説明をしていませんが、上記のAPEXアプリケーションにはOML4RのSQL APIを扱うページも含まれています。

PythonのUSER_PYQ_SCRIPTSに対応するRのビューUSER_RQ_SCRIPTSは、Pythonよりも検索に制限があります。

RのビューUSER_RQ_SCRIPTSでは、初期化フォームのプロセスでビューUSER_RQ_SCRIPTSを検索できません（０件になる）。

代わりにページ・ロード時に実行する動的アクションで、フォームの初期化を行なっています。

スクリプト名のP7_EXP_NAMを選択リストとして、LOVのSQL問合せにビューUSER_RQ_SCRIPTSを検索するSELECT文を書くと、検索結果が０行になります。このため、ビューUSER_RQ_SCRIPTS内容をAPEXコレクションに保存し、P7_EXP_NAMではAPEXコレクションを検索するように変更しています。

RもPythonと同じく、SQL APIを呼び出すSELECT文を動的に生成するケースはあまりないと思います。そのため、レポートの遅延ロードをオンにすることが必須の対応になります。

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

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

完

登録: 投稿 (Atom)