APEXで日時データを扱っていると、以下のエラーが発生することがあります。
ORA-01830: 日付書式の変換で不要なデータが含まれています
Oracle APEXのアプリケーションを作成する際の日時データの扱いについて、注意する点を思いつく範囲で紹介します。おおむね書式マスクの設定に関連します。数値にも書式マスクはありますが、一般的には日時データで問題になることが多いと思います。Oracleのドキュメントとしては、以下の部分に該当します。
SQL言語リファレンス リリース19c
2 Oracle SQL の基本要素
日時書式モデル
確認用アプリケーションを作成する
DATE型の列MY_DATE、TIMESTAMP型の列MY_TIMESTAMPなどを持つ表MY_TESTを作成します。SQLワークショップのSQLコマンドより実行します。
create table my_test(
my_level number
, my_date date
, my_timestamp timestamp
, my_local_date timestamp with local time zone
, my_global_date timestamp with time zone
);
現在日時の前後15日をテスト用のデータとして、表MY_TESTに投入します。
insert into my_test
select
level my_level
, (sysdate - (7 - level)) my_date
, (localtimestamp - (7 - level)) my_timestamp
, ((systimestamp - numtodsinterval((7 - level),'day'))) my_local_date
, ((systimestamp - numtodsinterval((7 - level),'day'))) my_global_date
from dual
connect by level <= 15;
テスト・データの準備ができたので、アプリケーション作成ウィザードを起動し、テストに使用するアプリケーションを作成します。アプリケーションの名前は日付書式の変換とします。
作成するアプリケーションには、クラシック・レポートとフォームのページ、および、対話グリッドのページを作成します。
ページの追加をクリックします。
ページの追加で（作成するのはクラシック・レポートですが）対話モード・レポートを選択します。
ページ名はレポートとフォームとします。表またはビュー、クラシック・レポートを選択し、表またはビューとして先ほど作成したMY_TESTを選択します。編集のためのページも作成するため、フォームを含めるにチェックを入れます。
ページの追加をクリックします。
再度ページの追加をクリックし、今度は対話グリッドを選択します。
ページ名はグリッドとします。表またはビュー、編集を許可を選択し、表またはビューとして対話モード・レポートと同様にMY_TESTを選択します。
ページの追加をクリックします。
アプリケーションの作成を実行します。
アプリケーションが作成されたらアプリケーション・プロパティの編集をクリックし、アプリケーション定義の画面を開きます。
グローバリゼーションの自動タイムゾーンをONに変更し、変更の適用をクリックします。
定義を開いて、アプリケーションの別名をTESTDATEMASKに変更します。
変更の適用をクリックします。
以上でテスト用のアプリケーションは完成です。
アプリケーションを実行し、作成されたアプリケーションを確認します。
レポートとフォームのページは以下のようになります。
対話グリッドのページです。
このアプリケーションを使用して、日時データの扱いの注意点ついて確認します。
一覧する日付の範囲を指定する
クラシック・レポートで一覧する行の表示条件として、開始日と終了日を設定できるようにします。
ページ・デザイナにて、クラシック・レポートが実装されているレポートとフォームのページを開きます。
リージョンMy Testにページ・アイテムP2_FROMとP2_TOを作成します。
リージョンMy Testの上でコンテキスト・メニューを表示させ、ページ・アイテムの作成を実行します。
作成したページ・アイテムの識別の名前をP2_FROM、タイプに日付ピッカーを選択します。ラベルは開始日とします。
もうひとつページ・アイテムを作成します。識別の名前はP2_TO、タイプは日付ピッカーでラベルは終了日です。レイアウトの新規行の開始はOFFにし、作成したページ・アイテムを横一列で配置します。
一覧する行を絞り込む条件を追加します。リージョンMy TestのソースのWHERE句として以下を記述します。
MY_DATE between :P2_FROM and :P2_TO
MY_DATE between :P2_FROM and :P2_TO
リージョンMy Testに送信ボタンを作成します。
リージョンMy Testの上でコンテキスト・メニューを表示させ、ボタンの作成を実行します。
識別のボタン名はB_SUBMIT、ラベルは送信とします。動作のアクションはデフォルトのページの送信のまま、変更はしません。ページ・アイテムP2_FROMおよびP2_TOの値がHTTPのPOSTでサーバーに送信され、その値が適用されて、レポートとフォームのページ全体が再表示されます。
アプリケーションを実行し、開始日と終了日に日付を設定して送信を実行します。
列MY_DATEの値が開始日と終了日の間にある行が一覧されます。
推奨１：書式マスクを個別に設定したページ・アイテムをSQLに含めるときは、TO_DATE関数などを使用して変換する。
アプリケーション定義のグローバリゼーションに設定されているデフォルトの日付書式やタイムスタンプ書式と異なる書式をページ・アイテムに適用するために、ページ・アイテムに書式マスクを設定します。ソースのSQLに現れるページ・アイテムにはデフォルトの書式が適用されるため、ページ・アイテムの書式マスクとは異なります。結果としてORA-1830が発生します。
そのため、書式マスクを個別に設定したページ・アイテムをSQLに含めるときは、TO_DATE、TO_TIMESTAMP、TO_TIMESTAMP_TZ関数などにページ・アイテムと同じ書式マスクを指定して日時データ型に変換する必要があります。
エラーの状況を確認してみます。
MY_DATE between to_date(:P2_FROM,'DD/MM/RRRR') and :P2_TO
レポートのソースのWHERE句にはページ・アイテムP2_FROMおよびP2_TOがバインド変数として使用されています。Oracle APEXのアイテムが保持しているデータは、必ず文字列です。
開始が2022/03/09、終了が2022/03/16と設定されているとします。
ソースの条件句は以下のように解釈されます。
MY_DATE between '2022/03/09' and '2022/03/16'
開始と終了の日付である文字列は列MY_DATEがDATE型なので、NLSセッション・パラメータNLS_DATE_FORMATに従ってDATE型に変換されます。
NLSセッション・パラメータのNLS_DATE_FORMATは、アプリケーション定義のグローバリゼーションのアプリケーション日付書式として設定されています。
デフォルトではDSと設定されているため、アプリケーションのプライマリ言語に依存して日付書式が決まります。日本語(ja)のアプリケーションではRRRR/MM/DDになります。
ソースとなるSQLまたはPL/SQLのコードにて、暗黙の変換が実施される場合はアプリケーション定義の設定が参照されます。
ページ・アイテムに、アプリケーション日付書式とは異なる書式マスクを設定してみます。
ページ・アイテムP2_FROMの書式マスクとしてDD/MM/RRRRを設定します。
ページを実行すると、ORA-1830が発生します。アプリケーション日付書式とページ・アイテムの書式マスクの設定が異なるためです。
例外としてはRRRR/MM/DDとRRRR-MM-DDといった、書式マスクの違いが区切り文字だけであれば（書式に厳密な定義であるFXをつけない限り）ORA-1830は発生しません。
SELECT文の条件句を以下のように変更すると、エラーの発生を回避できます。
MY_DATE between to_date(:P2_FROM,'DD/MM/RRRR') and :P2_TO
推奨２：グローバリゼーションに設定している書式は注意して変更する。
グローバリゼーションの日付書式などはアプリケーション全体に影響します。レポートの表示だけではなく、ページ・アイテムに書式マスクが設定されていて、ソースのSQLでページ・アイテムが暗黙で日時データに変換されていると、グローバリゼーションの日付書式が変更されることによって、ORA-1830が発生する可能性があります。
推奨１に従ってTO_DATE関数などをソースのSQLに使用していると、エラーの発生を避けることができます。
エラーの状況を確認してみます。
アプリケーション日付書式はDS HH24:MI（またはRRRR/MM/DD HH24:MI）と設定します。
ページ・アイテムP2_FROMの書式マスクもRRRR/MM/DD HH24:MIと設定します。
リージョンMy TestのソースのWHERE句に以下を記述します。
MY_DATE between :P2_FROM and :P2_TO
この場合、アプリケーションの日付書式とページ・アイテムP2_FROMの設定は一致しているため、エラーは発生しません。
時刻表示をデフォルトから外すために、アプリケーションの日付書式をDSに変更します。
結果として、ORA-1830が発生します。
エラーが発生するページ（今回の例ではレポートとフォームのページ）は一切変更していないので（特に他の人がアプリケーションの日付書式を変更した場合）、原因を見つけにくいエラーになります。
推奨３：アプリケーション日付書式に時間を含めるときは、タイムスタンプ書式にも時間を含める。
ソースのSQLの検索条件の列がDATE型でない場合、ページ・アイテムにはDATE型の書式（日付書式）、ソースのSQLでの暗黙変換では列の型に合わせた書式（例えばタイムスタンプ書式）が適用されます。そのため書式が異なるとORA-1830が発生します。
エラーの状況を確認してみます。
ページ・アイテムのタイプが日付ピッカーでソースのタイプがNULLの場合、ページ・アイテムの書式マスクが未指定であれば、アプリケーション日付書式が適用されます。
フォームのページ・アイテムであれば、以下のようにデータ型が指定されるので、この限りではありません。
今回使用しているアプリケーションに含まれるページ・アイテムP2_FROMやP2_TOといったページ・アイテムはデータ型が指定されていないので、アプリケーション日付書式が適用されます。
グローバリゼーションのアプリケーション日付書式としてDS HH24:MI、タイムスタンプ書式としてDSを設定します。
意識してこういう設定はしないと思います。一般にオラクルのDATE型は時刻を含むため、TIMESTAMP型はあまり使われません。そのため、DATE型の日付書式だけを変更し、タイムスタンプ書式はデフォルトを変更していないことはあり得ます。
範囲指定に時刻を含めるため、ページ・アイテムP2_FROMとP2_TOの設定の時間の表示をONにし、外観の書式マスクをRRRR/MM/DD HH24:MIにします。
本題から外れますが、日付ピッカーで時間の表示をONにすると、以下の問題が発生するので要注意です。
- 書式マスクを空白にできないため、デフォルトの日付書式が適用されない。
- APEX 21.2では書式マスクDSが正しく認識されない。
- 書式マスクTSは正しく認識されない。
この設定でソースのWHERE句として列MY_DATEではなくMY_TIMESTAMPが対象になっていると、0RA-1830が発生します。
MY_TIMESTAMP between :P2_FROM and :P2_TO
これは列MY_TIMESTAMPがTIMESTAMP型であるため、ページ・アイテムP2_FROMおよびP2_TOにNLS_TIMESTAMP_FORMAT（アプリケーションのタイムスタンプ書式）が適用されるためです。
デフォルトのタイムスタンプ書式を合わせるか、または、WHERE句にTO_TIMESTAMP関数を使う必要があります。
推奨４：DATE型のデータと比較する際にはTRUNC関数の利用を検討する。
特に意識せずDATE型のデータにアプリケーション日付書式が適用されていると、オラクルのDATE型が時間を含んでいることを忘れてしまうことがあります。結果として、範囲指定などが意図しない結果になります。
状況を確認してみます。
アプリケーション日付書式はDSとします。
ページ・アイテムP2_FROM、P2_TOの設定の時間の表示はOFFにします。時間の表示をOFFに切り替えても外観の書式マスクの設定は残るため、書式マスクの設定も削除します。
レポートの検索条件が以下で、P2_FROMに2022/03/09、P2_TOに2022/03/16を指定したケースを考えます。
MY_DATE between :P2_FROM and :P2_TO
TRUNC関数を使うようにWHERE句を変更します。
trunc(MY_DATE) between :P2_FROM and :P2_TO
グローバリゼーションのアプリケーション日付書式をDS HH24:MIに変更して実際のデータを確認してみます。
終了日として指定されている日付は厳密には2022/03/16 00:00なので、2022/03/16 02:15はそれより後の時刻になるため検索結果には含まれません。TRUNC関数を適用すると2022/03/16 02:15の時刻の部分は丸められて2022/03/16 00:00となるため、検索結果に含まれます。
推奨５：フォームのページ・アイテムに日時データがある場合、変更しなくても書式マスクが適用された日時で更新される。
日付のみが表示される書式マスクがページ・アイテムに適用されていると、フォームを保存したときに時刻の部分が00:00に更新されます。
状況を確認します。
レポートに含まれる日時データの列の書式マスクをDS HH24:MIに設定します。
それぞれの時刻データを確認します。00:00ではない時刻になっているはずです。
任意の一行を編集するため、フォームを開きます。
更新されたデータを確認すると、時刻の部分がすべて00:00になっていることが分かります。
グリッドのページを開いて任意の一行のLevel値を変更し、保存を実行します。
対話モード・レポートに戻って変更された行を確認すると、時刻の部分がすべて00:00になっていることが分かります。
DATE型のデータの表示を日付に限定しているにもかかわらず、アプリケーションの内部で時刻まで扱うようなコーディングをしていなければ問題は起こらないように思います。
推奨６：アプリケーションを多言語に対応させる場合、書式DSとRRRR/MM/DDといった書式を混在させない。
書式DSはアプリケーションのプライマリ言語の設定によって変わります。例えば日本語(ja)ではRRRR/MM/DDですが英語(en)ではDD/MM/RRRRです。ページ・アイテムの書式マスクにRRRR/MM/DDといった設定がされていると、プライマリ言語を変更したときにORA-18xxのエラーが発生することがあります。
エラーの状況を確認してみます。
グローバリゼーションのアプリケーションのプライマリ言語が日本語(ja)で、アプリケーション日付書式がDSとします。
アプリケーションのプライマリ言語を英語(en)に変更します。
書式マスクはDD/MM/RRRRとなるため、エラーが発生します。発生するエラーはORA-1843: not a valid monthになっていますが、原因は日時データとNLS_DATE_FORMATとの書式の不一致です。
APEX 21.2の日付ピッカーで時間の表示をONにすると、書式マスクDSが使えないようです。そのため、暗黙の変換を避けるような実装が必要です。
推奨７：日時データのページ・アイテムでタイムゾーンを扱わない。
アプリケーションのタイムスタンプ・タイムゾーン書式に含まれるTZRは、ページ・アイテムについては効果がありません。
状況を確認してみます。
グローバリゼーションのアプリケーションのタイムスタンプ・タイムゾーン書式をRRRR/MM/DD HH24:MI:SS TZRに設定します。
（APEX 21.2ではタイムスタンプ書式にDSを使用すると、ページ・アイテムに時刻が適切に表示されません。）
レポートを表示すると、TIMESTAMP WITH TIME ZONE型のデータにタイムゾーンが表示されていることが確認できます。
任意の一行を編集フォームで開きます。ページ・アイテムにはタイムゾーンは表示されません。
日時データは変更せずに（Levelだけを変更する）、変更の適用をします。レポートを確認すると、元々のデータが 2022/03/13 02:15:00 +00:00だったのが 2022/03/13 02:15:00 +09:00となっていることが確認できます。元々のタイムゾーンの代わりにアプリケーションのセッション・タイムゾーンが適用されたためです。
結果として、９時間、時間が進んでいます。TIMESTAMP WITH TIME ZONE型を使う場合には注意が必要です。
対話グリッドの場合、TIMESTAMP WITH TIME ZONE型の列にタイムゾーンが表示されていれば、タイムゾーンも含めて問題なく編集できます。
現時点で思いつく、Oracle APEXのアプリケーションで日時データを扱う際の注意点は以上になります。
ちなみに、デバッグ・ログより、NLS関連の設定はページ処理の一番最初に行われていることが分かります。
推奨８：ソースとなるSELECT文の列にTO_CHAR関数は使用しない。
今回のサンプルではリージョンMy Testのソースには表を指定しています。ソースのタイプをSQL問合せに切り替えると、以下のSELECT文がSQL問合せに現れます。
select ROWID,
MY_LEVEL,
MY_DATE,
MY_TIMESTAMP,
MY_LOCAL_DATE,
MY_GLOBAL_DATE
from MY_TEST
where MY_DATE between :P2_FROM and :P2_TO
日時データ型の書式を指定するために、TO_CHAR関数を以下のように使用すると、レポートとしては、その列が文字列型と認識されます。
select ROWID,
MY_LEVEL,
to_char(MY_DATE,'RRRR/MM/DD') MY_DATE,
MY_TIMESTAMP,
MY_LOCAL_DATE,
MY_GLOBAL_DATE
from MY_TEST
where MY_DATE between :P2_FROM and :P2_TO
特に対話モード・レポートにおいて、日時データ型に適用できるフィルタ条件が変わるため注意が必要です。
列が日時データ型として認識されている場合のフィルタ条件です。
TO_CHAR関数を適用した場合は、列の表示はまったく同じに見えても文字列型として扱われます。
文字列型として認識されると、ページ・アイテムの書式マスクおよびデフォルトの日付書式は適用されません。
推奨９：デフォルトの指定にTO_CHAR関数および文字列は使わない
Oracle APEX 21.2で発生する事象で、Oracle APEX 21.1では発生しません。とはいえ、最初からデフォルトにTO_CHARおよび文字列を使っていなければ、両方のバージョンで安全です。
日付を保持するページ・アイテムのデフォルトを、現在の日付やその前後の日付に設定する場合は多いと思います。TO_CHARを使って書式を指定すると、結果は文字列になります。デフォルトが文字列の場合、ページ・アイテムには文字列がそのまま設定されます。
TO_CHARで指定している書式とページ・アイテムの書式マスクまたはアプリケーション定義のアプリケーション日付書式が一致している場合は問題は発生しませんが、ページ・アイテムの書式マスクまたはアプリケーション日付書式が変更されるとエラーが発生します。
特にアプリケーション日付書式を変更している場合、ページ自体は変更していないし、また、利用者側から見ても違いはないため、原因が分かりにくいエラーになります。
ページ・アイテムのデフォルトとしてタイプが式、PL/SQL式として
to_char(sysdate,'YYYY/MM/DD')
が設定されています。
デフォルトを確認するために、新規作成でフォームを開くと2022/03/25のように値が設定されます。to_char(sysdate,'YYYY/MM/DD')の結果が設定されています。
グローバリゼーションのアプリケーション日付書式を、DSからDD/MM/RRRRへ変更します。
再度、フォームを開いて確認します。デフォルトは変わらず2022/03/25です。そのため、この日付でそのまま保存しようとするとORA-18xxのエラーが発生します。
開発者が意図してYYYY/MM/DDの書式にしているので、それをそのままページ・アイテムに設定しているAPEX 21.2の動作の方が理にかなっています。
デフォルトの値をソースのデータ型に一致させると、このようなエラーは発生しません。
現在の日時をデフォルトにする場合であれば、DATE型はSYSDATEまたはCURRENT_DATE、TIMESTAMP型はSYSTIMESTAMP（タイムゾーン有り）、CURRENT_TIMESTAMP（タイムゾーン有り）、LOCALTIMESTAMP（タイムゾーン無し）などを使うことになります。
以上になります。
今回テストに使ったアプリケーションのエクスポートを以下に置きました。
https://github.com/ujnak/apexapps/blob/master/exports/testdatemask.sql
Oracle APEXのアプリケーション作成の参考になれば幸いです。
完
