APEX 22.1で追加された行検索のトークン化の効果

 Oracle APEX 22.1よりアプリケーション定義のプロパティに、行検索のトークン化が追加されています。

オンライン・ヘルプには、以下のように説明されています。

コンポーネントで行検索が行われた場合に、検索語がどのように適用されるかを指定します。

「オン」を選択すると、検索語のそれぞれの単語が別々に処理されます。全体であれ個別であれ、列に検索語が含まれていればレコードが一致します。フレーズ全体を検索するには、検索語を二重引用符(")で囲み、"ui developer"のようにします。検索語の中の二重引用符をエスケープするには、二重引用符("")を使用します。
例1- redおよびshoesという語を含む任意のレコード検索: red shoes2- developerという語とui designerという表現全体を含む任意のレコード検索: developer "ui designer"3- authorという語と、the sky is "blue" and niceという表現全体を含む任意のレコード検索語: author "the sky is ""blue"" and nice"

「オフ」を選択すると、検索語全体が完全一致として扱われます。

Oracle APEX 22.1で新規に作成したアプリケーションでは、デフォルトでONになっています。

こちらの記事で作成した簡易ファイル管理アプリケーションを使って、行検索のトークン化の効果を確認します。

今回の動作確認では、対話モード・レポートを使用します。これ以外にスマート・フィルタ、ファセット検索、対話グリッド、ポップアップLOVなど、行検索がサポートされているすべてのコンポーネントで機能します。

テストに使用するデータとして、Oracle APEX情報サイトよりPDFで提供されているファイルをいくつかアップロードしています。

Oracle Textによる全文検索との関係

レポートにOracle Text索引列が設定されている場合は、Oracle Textによる検索が優先されます。この場合、行検索のトークン化の設定は意味を持ちません。

対話モード・レポートでは、属性の詳細のOracle Text索引列に、Oracle Text索引が作成されている列を指定します。今回のサンプル・アプリケーションでは、表SIMPLE_CONTENTSの列TITLEを指定します。

Oracle and Daysを検索語として行検索を行います。OracleとDaysを含む文書が３件検索されました。

列Abstractを非表示にしても、検索結果は変わりません。これは全文検索索引を作成する元データとして、列TITLE、ABSTRACT、そしてBLOBから自動フィルタを適用して取り出した文字列を使用しているためです。逆に列TITLE、ABSTRACT、アップロードしたファイルの内容以外はレポートに表示されていても、検索対象にはなりません。

デバッグ・ログより実際に実行されているSELECT文を確認してみます。

もっとも内側で実行されているSELECT文の、WHERE句として与えられている条件は以下になります。

contains("TITLE",:apex$f1,1)>0

バインド変数の:apex$f1に、検索語のOracle and Daysが渡されています。

行検索のトークン化がOFFの場合

Oracle Daysを検索語として行検索を行います。結果は０件になります。行検索のトークン化がOFFの場合、検索語Oracle Daysと完全一致する単語を含む行のみが検索結果となります。

同様にデバッグ・ログよりWHERE句の条件を確認します。

(

    instr( upper("TITLE"),upper(:apex$f1)) > 0 

    or 

    instr( upper("ABSTRACT"),upper(:apex$f1)) > 0

)

バインド変数の:apex$f1に、検索語のOracle Daysが渡されています。

この条件であれば、検索結果は０件になります。

行検索のトークン化がONの場合

APEX 22.1で追加されたプロパティ行検索のトークン化をONにしたときの結果です。OFFの場合と同様に検索語としてOracle Daysを与えて行検索を行います。

行検索のトークン化がONの場合、検索結果が３件になりました。

デバッグ・ログよりWHERE句の条件を確認します。

(

    instr( upper("TITLE"),upper(:apex$f1)) > 0

    or

    instr( upper("ABSTRACT"),upper(:apex$f1)) > 0

)

and

(

    instr( upper("TITLE"),upper(:apex$f2)) > 0 

    or

    instr( upper("ABSTRACT"),upper(:apex$f2)) > 0

)

行検索に与えられた検索語Oracle Daysは空白で区切られ、Oracleは:apex$f1、Daysは:apex$f2に割り当てられます。結果としてOracleとDaysが含まれている表示列があれば、検索結果に含まれます。

今回は列ABSTRACTにOracleとDaysの両方が含まれているデータが３件ありました。

Oracle Text関数

本題から外れますが、近いトピックなのでOracle Text関数も使ってみます。

ファンクションconvert_end_user_searchを作成します。

create or replace function convert_end_user_search (
    p_search in varchar2 )
    return varchar2 
is
begin
    return 'FUZZY({' || replace( p_search, '}', '\}' ) || '}, 30, 2000)';
end;

これはOracle Text関数のオンライン・ヘルプに記載のある、Oracle Text CONTAINS問合わせ演算子のFUZZY関数を使った例です。

アプリケーション定義のプロパティのOracle Text関数として、convert_end_user_searchを設定します。

対話モード・レポートの属性のOracle Text索引列にTITLEを指定し、Oracle Textによる全文検索を有効にします。

検索語としてOlacle Daysを与えて、行検索を行ってみます。Oracleではなくスペルが間違っているOlacleです。

FUZZY検索が行われるため、スペルが間違っていても、検索結果として３件返されます。

これも条件句を確認してみます。

contains("TITLE",convert_end_user_search(:apex$f1),1)>0

convert_end_user_searchが返す文字列は、:apex$f1がOlacle Daysであれば以下になります。

FUZZY({Olacle Days}, 30, 2000)

結果として、上記の条件句は以下になります。

contains("TITLE",'FUZZY({Olacle Days}, 30, 2000)',1)>0

この他にも多数の演算子があります。しかし、Oracle Textの日本語対応は充実しているとは言い難いため、ほとんど期待できないです。

Oracle APEX 22.1で追加された、行検索のトークン化についての説明は以上になります。

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

完

APEX 22.1で変更されたAutonomous Databaseでのワークスペース作成

 以前にAutonomous Databaseでのワークスペースの追加作成という記事で、Autonomous DatabaseでのAPEXワークスペースの作成手順について説明しました。

Autonomous DatabaseのAPEXがOracle APEX 22.1に上がった際に、APEXワークスペースの作成手順が変更されています。

以下より変更された手順を確認します。

管理サービスの説明「自律型データベースの管理者(ADMIN)パスワードを使用してサインインしてください。」に従って、ADMINのパスワードを入力し、管理にサインインをクリックします。

初回サインイン時はワークスペースが未作成のため、すぐにワークスペースを作成するダイアログが開きます。今回はワークスペースはすでに作成済みなので、ワークスペースを追加作成します。

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

ダイアログが開きます。新規のスキーマを選択します。

ワークスペースの作成のダイアログが開きます。

APEX 22.1以前のダイアログは以下です。

APEX 22.1から、以下のダイアログに変わりました。

ワークスペース名については、バージョンによらず双方ともAPEXのワークスペース名の指定になります。

以前あったデータベース・ユーザとパスワードの指定は無くなりました。

APEX 22.1からは、ワークスペースの管理者ユーザーとして作成されるデータベース・ユーザーと、データベース・オブジェクトなどを保持しデフォルト・パーシング・スキーマとなるデータベース・ユーザーは別に作成されます。

ワークスペース・ユーザー名およびパスワードとして、ワークスペースの管理者となるデータベース・ユーザーの名前とパスワードを指定します。このユーザーは、データベース・オブジェクトを保持しません。

データベース・オブジェクトを保持するスキーマとして、ワークスペース名に接頭辞としてWKSP_を付けたデータベース・ユーザーが作成されます。データベース・パスワードは、このユーザーに与えるパスワードです。sqlplusやSQLclといったツールから接続する要件がなければ、このパスワードを指定する必要はありません。

ワークスペースIDは、こちらの記事で説明しているように、開発、ステージング、本番といった異なる用途のインスタンスに作成するワークスペースで、ワークスペースIDを一致させる際に指定します。

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

作成されたワークスペースを確認します。

既存のワークスペースからは、ワークスペースHRが作成されていることが確認できます。

開発者とユーザーの管理を確認します。

ワークスペース管理者としてHRADMINが作成されています。ワークスペースはHR、デフォルトのスキーマとしてWKSP_HRが紐づけられています。

作成されたワークスペースにサインインします。

サインインには、ワークスペース名、ワークスペース・ユーザー名、ワークスペース・パスワードを指定します。データベース・ユーザー名（WKSP_HR）およびデータベース・パスワードではサインインできません。

SQLワークショップのSQLコマンドを開き、以下のSQLを実行します。

select 
    sys_context('userenv','session_user') as session_user, 
    sys_context('userenv','session_schema') as session_schema,
    sys_context('userenv','current_schema') as current_schema,
    sys_context('userenv','proxy_user') as proxy_user
from　dual;

SQLコマンドを実行するスキーマとして、右上の選択リストにWKSP_HRが選択されていることが確認できます。

Oracle APEXによるSQLの実行という解説記事を書いた当時は、APEXの処理であるプロシージャはSESSION_USERのORDS_PUBLIC_USERから呼び出されていました。これはセキュリティ強化を目的として、Oracle REST Data Servicesの処理と同様にORDS_PUBLIC_USERをプロキシとして通した上で、ユーザーORDS_PLSQL_GATEWAYにて実行されるように変わっています。

続けて、Oracle REST Data Servicesを有効にします。

Oracle REST Data Servicesを有効にするスキーマは、WKSP_HRになります。HRADMINではありません。

ORDSにスキーマを登録する際に、スキーマ別名をワークスペース名に一致するようにします。今回の例ではhrです。スキーマ名であるWKSP_HRはORDS RESTサービスのURLに現れません。

データベース・アクションから、作成されたデータベース・ユーザーを確認します。

RESTの有効化が行われているのはWKSP_HRです。そのため、データベース・アクションへの接続URLはデータベース・ユーザーWKSP_HRに対して生成されています。

リンクを開き、データベース・ユーザーWKSP_HRでADBに接続します。

画面が開きます。ここで入力するユーザーはAPEXワークスペースの管理者ユーザーとパスワードになります。

今回の例ではHRADMINとそのパスワードを入力します。

サインインにはAPEXワースクスペースの管理者ユーザーであるデータベース・ユーザーのユーザー名とパスワードを指定しましたが、データベース・アクションとしてはデータベース・ユーザーWKSP_HRとしてサインインされています。

以上より、Oracle APEXとデータベース・アクションの双方で、ワークスペースのスキーマWKSP_HRを直接指定して接続することはありません。

直リンクによる接続でない場合は、最初のユーザー名として、ORDS別名であるHRを入力します。

Oracle APEXおよびOracle REST Data Servicesの双方とも、実際にSQLを実行するユーザーはWKSP_HRになるため、grant文を呼び出して実行権限などを付与する対象は、WKSP_HRになります。

データベース・アクションには、Oracle APEXのワークスペースを管理する機能が追加されています。

画面右上に、ワークスペースの作成というボタンがあります。

現時点（2022年7月12日）のデータベース・アクションでは、データベース・ユーザー（スキーマ）の名前と、APEXユーザー名を同じ名前にしてAPEXワークスペースを作成します。これはAPEX側から見ると、APEX 22.1以前でのワークスペースの作成方法になります。

Autonomous Database上で動作するAPEX 22.1から、ワークスペースを新規作成する場合、スキーマにはWKSP_の接頭辞が付くことを理解した上で、Oracle APEXで扱うスキーマ名にWKSP_といった接頭辞は付けたくない場合は、APEXではワークスペースを新規作成せず、既存のスキーマを選択するか（あらかじめスキーマを作成するには、データベース・アクションでの作業が必要）、そのままデータベース・アクションからAPEXワークスペースを作成する必要があるでしょう。

APEX 22.1で変更された、Autonomous Databaseでのワークスペース作成についての説明は以上です。

完

APEX 22.1の承認コンポーネント(3) - ユーザー・データの利用

 前回の記事では、承認コンポーネントを利用して休暇申請の承認と却下のフローを実装しました。しかし、これだけではアプリケーションとして動作はしますが、データとしては何も残りません。

休暇申請を保存する表を作成し、申請とその結果を保存するようにします。

SQLワークショップのユーティリティに含まれているクイックSQLを使って、以下のモデルから表UNI_REQUETSを作成します。

uni_requests
    reason vc160
    start_date
    end_date
    status vc10
    requestor vc80
    request_date
    approver vc80
    approve_date

上記のモデルをクイックSQLに記述し、SQLの生成、SQLスクリプトの保存、レビューおよび実行を順次実施します。

この後、生成されたSQL（DDL）の即時実行までを実施します。アプリケーションはすでに作成済みのものを改変するため、アプリケーションの作成は行いません。

共有コンポーネントのタスク定義休暇申請を開き、タスクにたいして実施する操作をユーザーが用意した表UNI_REQUESTSに反映されるよう、設定を変更します。

設定のアクション・ソースとして、表UNI_REQUESTSを指定します。

アクション・ソースとして表を選択します。表の所有者はデフォルトの解析対象スキーマ、表名としてUNI_REQUESTS(表)を選択します。アクション表の主キー列として、表UNI_REQUESTSの主キー列である、ID(Number)を選択します。

この設定は、アクション・ソースにSQL問合せを選択し、アクションSQL問合せとして以下を記述したことと同じです。

select * from uni_requests where id = :APEX$TASK_PK

アクション・ソースに複数の表を含めたい場合などは、アクション・ソースとしてSQL問合せを選びます。

設定の件名は、変更したアクション・ソースを使うようにします。

&REASON. &START_DATE. - &END_DATE.　&REQUESTER.

この後に定義済みのパラメータをすべて削除するため、&AC_REASON.、&AC_START_DATE.および&AC_END_DATE.の代わりに表UNI_REQUESTSから得られる値&REASON.、&START_DATE.および&END_DATE.を使います。標準の置換文字列である&APEX$TASK_INITIATOR.は有効ですが、表UNI_REQUESTSに登録されている申請者を確認できるように、表UNI_REQUESTSに保存されている値である&REQUESTER.を使います。

以上の変更は、アプリケーションの動作と見かけには影響しません。

参加者の潜在的所有者を、静的な割り当てからPL/SQLのファンクション本体による割り当てに変更します。実用面を考慮すると、潜在的所有者を静的に割り当てるケースは稀でしょう。

今回は簡単に、申請花子さんの休暇申請は承認花子さんが承認者（所有者 - Actual Owner）、申請太郎さんの休暇申請の承認者は承認太郎さんが承認者となるようにします。それ以外は管理者が承認します。

以下のコードを実装します。

begin
  if :APEX$TASK_INITIATOR = '申請太郎' then
      return '承認太郎';
  elsif :APEX$TASK_INITIATOR = '申請花子' then
      return '承認花子';
  else
      return '管理者';
  end if;
end;

パラメータはすべて削除します。

今回は、全てのパラメータを永続的に保存することにしました。タスクの作成から完了まで値を保持できればよく、タスクの完了後にタスクとともにパージされて問題がなければ、パラメータを利用します。

タスクが承認されたとき、または却下されたときに実行されるアクションを作成します。

アクションの追加をクリックします。

休暇申請が承認されたときに実行するコードを登録します。

アクションの名前は承認、タイプとしてコードを実行を選択します。コードの実行の他に電子メールの送信を選ぶこともできます。アクションが実行されるタイミングであるイベント時として、完了を選択します。完了以外に、タスクにたいして行われる操作に対応した、作成、要求、委任、取消、リリース、コメントの更新、優先順位の更新も選択することができます。結果として承認済を選択します。

コードには以下を記述します。

begin
    update uni_requests 
        set status = 'APPROVED'
        , approver = :APEX$TASK_OWNER
        , approve_date = sysdate
    where id = :APEX$TASK_PK;
    commit;
end;

以上の設定で、タスクが承認されたときに表UNI_REQUESTSに保存されている休暇申請の列STATUSがAPPROVEDとして更新されます。それと同時に承認者(APPROVER)と承認日(APPROVE_DATE)も更新されます。

却下のアクションでは、結果に却下済を選択し、コードは列STATUSにREJECTEDを更新するようにします。

begin
    update uni_requests 
        set status = 'REJECTED'
        , approver = :APEX$TASK_OWNER
        , approve_date = sysdate
    where id = :APEX$TASK_PK;
end;

以上で、タスク定義の更新は完了です。

Autonomous Databaseの場合、SYSDATEはUTCでの時刻を返します。ローカル（例えば東京）の時刻を扱う場合は、セッション・パラメータSYSDATE_AT_DBTIMEZONEが有効になるように構成します。このような場合にアクションのコード中でSYSDATEを扱う際には、SYSDATE_AT_DBTIMEZONEを明示的に有効にする必要があるかもしれません。

begin
    execute immediate 'alter session set sysdate_at_dbtimezone = true';
    update uni_requests 
        set status = 'APPROVED'
        , approver = :APEX$TASK_OWNER
        , approve_date = sysdate
    where id = :APEX$TASK_PK;
    commit;
end;

タイムゾーン付きの日付データ型（TIMESTAMP WITH LOCAL TIME ZONE, TIMESTAMP WITH TIME ZONE）を使用している場合は、こういった考慮は不要になります。

休暇申請を行うページを修正します。タスクの生成に加えて、表UNI_REQUESTSへの休暇申請の書き込みを行い、生成したタスクと紐づけます。

ページ・デザイナでホーム・ページを開きます。

リージョン休暇申請を選択し、作成済みのページ・アイテムをすべて選択し削除します。ボタンB_SUBMITはそのまま残します。

リージョン休暇申請の識別のタイプを、静的コンテンツからフォームへ変更します。その後、ソースの表名としてUNI_REQUESTSを選択します。

ページ・アイテムP1_IDを選択し、識別のタイプを非表示に変更します。ソースの主キーはONにします。このページ・アイテムが、作成した休暇申請（表UNI_REQUESTSに含まれる１行）を一意に特定する値を保持します。

ページ・アイテムP1_STATUS、P1_APPROVER、P1_APPROVE_DATEを選択し、ビルド・オプションを使ってコメント・アウトします。これらのページ・アイテムに対応する列STATUS、APPROVER、APPROVE_DATEはタスクのアクションによって設定され、画面からは設定しません。

ページ・アイテムP1_REQUESTERを選択し、タイプを非表示にします。デフォルトのタイプにアイテムを選択し、アイテムにAPP_USERを指定します。休暇申請の申請者は、かならずサインインをしているユーザーになります。

ページ・アイテムP1_REQUEST_DATEにも同様にデフォルト値を設定します。識別のタイプを非表示にし、デフォルトのタイプとして式、言語にSQLを選択し、SQL式としてsysdateを記述します。

ボタンB_SUBMITを選択し、動作のデータベース・アクションとしてSQL INSERT操作を選択します。今回のフォームは休暇申請の作成のみを行います。この処理は、表UNI_REQUESTSへのINSERT処理に当たります。

プロセス・ビューに移り、タスクを作成するプロセス休暇申請を選択します。設定のディテール主キー・アイテムとしてP1_IDを選択します。この指定により作成されるタスクと表UNI_REQUESTS内の行が紐づけられます。

作成されたタスクは標準ビューAPEX_TASKSより確認できます。ディテール主キー・アイテムとして与えられた値は、列DETAIL_PKから確認できます。

select task_id, subject, detail_pk from apex_tasks where task_def_static_id = 'LEAVE_REQUEST' and detail_pk is not null;

フォームを処理するプロセスを追加します。プロセスを新たに作成し、休暇申請の上に配置します。

識別の名前をプロセス - フォーム休暇申請とします。タイプとしてフォーム - 行の自動処理(DML)を選択し、フォーム・リージョンとして休暇申請を選びます。サーバー側の条件のボタン押下時にB_SUBMITを指定します。

以上で、一通りの実装は完了しました。

表UNI_REQUESTSの内容を確認するために、対話モード・レポートのページを作成します。

ページ作成ウィザードを起動し、対話モード・レポートを選択します。

名前は休暇申請一覧とします。ページ・モードは標準、編集はしないのでフォーム・ページを含めるはOFFです。データ・ソースの表/ビューの名前としてUNI_REQUESTSを選択します。このページをサイド・メニューから呼び出せるように、ナビゲーションはONにし、ナビゲーションのプリファレンスとして、エントリを新規作成しますを選択します。

おおむね以上の設定にて、ページの作成を実行します。

作成したページを実行すると、以下のように表示されます。

承認コンポーネント単体で複雑な処理フローを実装できるわけではありませんが、タスク定義を作成することにより、画面上のボタンを押したときに実行する処理を、プロセスとしてページに作成する以外に、タスク定義のアクションとして作成することができるようになりました。結果として、複数のページより実行する処理を、タスク定義のアクションにまとめることが可能になっています。今回の例でいうと、私のタスクから休暇申請を承認できるし、タスク詳細のページから休暇申請を承認することもできますが、実行されるコードはタスク定義のアクションとして記述されています。アクションのコードを変更すると、どのページで承認を行うかにかかわらず、すべての承認処理が変更されます。

今回作成したAPEXアプリケーションを以下に起きました。

https://github.com/ujnak/apexapps/blob/master/exports/simple-approvals-component-ud.sql

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

完