日日是Oracle APEX: 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のアプリケーション作成の参考になれば幸いです。

完

2025年7月8日火曜日

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

APEX環境の作成

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