はじめに
現在日時を取得するのにsysdate関数を利用しているアプリケーションがよくあります。
sysdateのドキュメントを見ると、「SYSDATEは、データベース・サーバーが存在するオペレーティング・システムの現在の日付と時刻のセットを戻します。」となっており、DBサーバのOS時間の現在日時を返すとなっています。
また、共有インフラストラクチャのAutonomous Databaseは、その名の通り基盤であるExadataを複数のテナントで共有して使用するため、OS時間はUTC固定となっており変更できません。
結果として、Autonomous Databaseではsysdateは常にUTCでの現在日時を返すことになります。
これにより、sysdateが現在日時をJSTで返す前提のアプリケーションのDBをAutonomous Databaseに移行するにはアプリケーションの改修が必要となり、Autonomous Databaseへの移行の障壁となっていました。
今回はOracle 12cから利用可能なSQL翻訳フレームワークを使用して、Autonomous DatabaseでsysdateがJSTでの現在日時を返すような仕掛けを検証してみました。
注意：
こちらの内容はあくまで当方の環境で検証した結果ですので、ご利用に関しては自己責任でお願いします。
本番環境に適用する際は、必ず事前に充分なテストを実施してください。
1.SQLトランスレータの準備
Autonomous Databaseにadminユーザで接続し、SQLトランスレータのパッケージ「data_translator」を作成します。
CREATE OR REPLACE PACKAGE admin.date_translator IS
procedure translate_sql(sql_text in clob, translated_text out clob );
procedure translate_error(error_code in binary_integer, translated_code out binary_integer, translated_sqlstate out varchar2);
END;
/
SQLトランスレータのパッケージ「data_translator」のパッケージボディを作成します。
パッケージボディには、SQL_TEXT内のSYSDATEをCURRECT_DATEに、SYSTIMESTAMPをCURRENT_TIMESTAMPに書き換えるtranslate_sqlプロシージャとエラーハンドリングのためのtranslate_errorプロシージャが含まれます。
CURRENT_DATEはDBサーバのOS時間の現在日時をセッションタイムゾーン変換したものを戻します。
CREATE OR REPLACE PACKAGE BODY admin.date_translator IS
PROCEDURE translate_sql(sql_text in clob, translated_text out clob ) IS
BEGIN
translated_text := regexp_replace(sql_text, 'SYSDATE', 'CURRENT_DATE', 1, 0, 'i');
translated_text := regexp_replace(translated_text, 'SYSTIMESTAMP', 'CURRENT_TIMESTAMP', 1, 0, 'i');
END;
PROCEDURE translate_error(error_code in binary_integer, translated_code out binary_integer, translated_sqlstate out varchar2) IS
BEGIN
null;
END;
END;
/
作成したSQLトランスレータを使用するSQL翻訳プロファイル「DATE_TRANSLATION_PROFILE」を作成します。
BEGIN
-- Create SQL Translation Profile
dbms_sql_translator.create_profile(profile_name => 'DATE_TRANSLATION_PROFILE');
-- Set the translator function
dbms_sql_translator.set_attribute(profile_name => 'DATE_TRANSLATION_PROFILE',
attribute_name => dbms_sql_translator.attr_translator,
attribute_value => 'ADMIN.DATE_TRANSLATOR');
-- Make translator work for Oracle SQL
dbms_sql_translator.set_attribute(profile_name => 'DATE_TRANSLATION_PROFILE',
attribute_name => dbms_sql_translator.ATTR_FOREIGN_SQL_SYNTAX,
attribute_value => dbms_sql_translator.ATTR_VALUE_FALSE);
END;
/
作成したSQL翻訳プロファイルを使用するのに必要な権限をPUBLICに付与します。
GRANT ALL PRIVILEGES ON SQL TRANSLATION PROFILE admin.date_translation_profile TO PUBLIC;
GRANT EXECUTE ON admin.date_translator TO PUBLIC;
2.SQLトランスレータのテスト
セッションのタイムゾーンを「Asia/Tokyo」に設定します。
SQL> ALTER SESSION SET TIME_ZONE='Asia/Tokyo';
セッションが変更されました。
SQL>
set time onでSQL*Plusのプロンプトにクライアント（タイムゾーンはAsia/Tokyo）の現在時刻が表示されるようにします。
SQL> set time on
17:51:11 SQL>
NLS_DATE_FORMATを「YYYY/MM/DD HH24:MI:SS」に変更しておきます。
17:53:57 SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';
セッションが変更されました。
17:53:59 SQL>
まずはSQLトランスレータが無効の状態でSYSDATE、SYSTIMESTAMPの値を確認してみます。
17:53:59 SQL> SELECT sysdate FROM dual;
SYSDATE
-------------------
2021/10/05 08:55:17
17:55:17 SQL> SELECT systimestamp FROM dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
21-10-05 08:55:26.190798 +00:00
17:55:26 SQL>
Autonomous Databaseのオペレーティング・システムの現在日時（UTC）が返ってきます。
次にSQLトランスレータをセッションレベルで有効にしてテストしてみます。
SQLトランスレータをセッションレベルで有効にします。
17:57:46 SQL> ALTER SESSION SET SQL_TRANSLATION_PROFILE=admin.date_translation_profile;
セッションが変更されました。
17:57:48 SQL>
SQLトランスレータが有効になっている状態でSYSDATE、SYSTIMESTAMPの値を確認してみます。
17:58:22 SQL> SELECT sysdate FROM dual;
CURRENT_DATE
-------------------
2021/10/05 17:59:37
17:59:37 SQL> SELECT systimestamp FROM dual;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
21-10-05 17:59:44.160323 ASIA/TOKYO
17:59:44 SQL>
SYSDATE、SYSTIMESTAMPともに、現在時刻が日本時間で返ってきました！
3.ログオン・トリガーを使用したSQLトランスレータの有効化
SQLトランスレータを有効にすることで、SYSDATE、SYSTIMESTAMPが日本時間を返すようにすることができたので、こちらをアプリケーションユーザのログオン・トリガーに実装してみます。
ここでは、「apuser」というアプリケーションユーザのログオン・トリガーでSQLトランスレータを有効にしてみます。
まずは「apuer」というDBユーザを作成します。
SQL> GRANT CONNECT, RESOURCE to apuser IDENTIFIED BY Demo#1Demo#1;
権限付与が成功しました。
SQL>
apuserのログオン・トリガー「date_translation_trigger」を作成します。
SQL> CREATE OR REPLACE TRIGGER apuser.date_translation_trigger
2 AFTER LOGON ON apuser.schema
3 BEGIN
4 execute immediate 'ALTER SESSION SET SQL_TRANSLATION_PROFILE=admin.date_translation_profile';
5 execute immediate 'ALTER SESSION SET TIME_ZONE=''Asia/Tokyo''';
6 END;
7 /
トリガーが作成されました。
SQL>
adminユーザからログアウトします。
SQL> exit
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.1.0との接続が切断されました。
[opc@work1 ~]$
apuserでDBにログインします。
[opc@work1 ~]$ sqlplus apuser/Demo#1Demo#1@atp1_high
SQL*Plus: Release 19.0.0.0.0 - Production on 火 10月 5 18:32:50 2021
Version 19.11.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.1.0
に接続されました。
SQL>
set time onでプロンプトにクライアントの現在時刻（タイムゾーンはAsia/Tokyo）を表示します。
SQL> set time on
18:34:38 SQL>
セッションのNLS_DATE_FORMATを「YYYY/MM/DD HH24:MI:SS」変更します。
18:34:38 SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';
セッションが変更されました。
18:35:20 SQL>
SYSDATE、SYSTIMESTAMPの値を確認してみます。
18:36:48 SQL> SELECT sysdate FROM dual;
CURRENT_DATE
-------------------
2021/10/05 18:36:49
18:36:49 SQL> SELECT systimestamp FROM dual;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
21-10-05 18:37:02.156946 ASIA/TOKYO
18:37:02 SQL>
ログオン・トリガーでSQLトランスレータが有効になっているので、「ALTER SESSION SET SQL_TRANSLATION_PROFILE」文で明示的にSQLトランスレータを有効にしなくても、SYSDATE、SYSTIMESTAMPともに、現在日時が日本時間で返ってきました。
まとめ
SQL翻訳フレームワークを使用して、Autonomous Databaseでsysdateが現在日時をJSTで返すようにすることができました。
ただし、実行結果を見るとわかりますが、SQL実行時にSQL文中のSYSDATEがCURRENT_DATEに、SYSTIMESTAMPがCURRENT_TIMESTAMPに書き換えられて実行されているため、結果の列名がそれぞれCURRENT_DATE、CURRENT_TIMESTAMPになっている点は注意が必要です。
なお、SYSDATE、SYSTIMESTAMPを含むプロシージャ、ビューは再作成が必要になります。
また、名前にSYSDATE、SYSTIMESTAMPを含むオブジェクトもSQLトランスレータが有効な状態では参照できなくなってしまいますのでご注意ください。
参考情報
Oracle Database Release 19 SQL翻訳および移行ガイド：SQL翻訳フレームワークの概要
Oracle Database Release 19 SQL言語リファレンス：SYSDATE