[20210419]測試18c SQL Translation Framework.txt

[20210419]測試18c SQL Translation Framework.txt

--//據說18c做了一點點增強，不用設定  alter session set events = '10601 trace name context forever, level 32';
--//可以參考我以前的連結：
--//http://blog.itpub.net/267265/viewspace-2216487/ => 12C SQL Translation Framework.txt

1.環境：
xxxx> @ ver1
xxxx> @ prxx
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 18.0.0.0.0
BANNER                        : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
BANNER_LEGACY                 : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.建立SQL Translation Framework：

begin
   dbms_sql_translator.create_profile('test_profile');

   /* This attribute indicates if the profile is for traslation
           of foreign SQL syntax only. if True only foreign sql
           will be translated, if false, all sql from client
           application will be translated */

   dbms_sql_translator.set_attribute(
           profile_name=>'test_profile',
           attribute_name=> dbms_sql_translator.ATTR_FOREIGN_SQL_SYNTAX,
           attribute_value=> dbms_sql_translator.ATTR_VALUE_FALSE );

   dbms_sql_translator.register_sql_translation(
           profile_name=>'test_profile',
           sql_text=>'select sysdate',
           translated_text=>'select sysdate from dual' );
end;
/
--//主要增加設定屬性的內容。

3、測試：
xxxx> select sysdate;
select sysdate
             *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
--//還沒有開啟.

xxxx> alter session set sql_translation_profile=test_profile;
Session altered.

xxxx> select sysdate;
SYSDATE
-------------------
2021-04-19 10:34:34

--//ok,測試透過。
xxxx> Select sysdate;
Select sysdate
             *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

--//內容必須要與sql_text定義一致。

4.涉及檢視:
DBA_ERROR_TRANSLATIONS
DBA_SQL_TRANSLATION_PROFILES
DBA_SQL_TRANSLATIONS

xxxx> select * from DBA_ERROR_TRANSLATIONS;
no rows selected

xxxx> select * from DBA_SQL_TRANSLATION_PROFILES
  2  @ prxx
==============================
OWNER                         : TTT
PROFILE_NAME                  : TEST_PROFILE
TRANSLATOR                    :
FOREIGN_SQL_SYNTAX            : FALSE
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TRANSLATE_NEW_SQL             : TRUE
RAISE_TRANSLATION_ERROR       : FALSE
LOG_TRANSLATION_ERROR         : FALSE
TRACE_TRANSLATION             : FALSE
LOG_ERRORS                    : FALSE
PL/SQL procedure successfully completed.
--//視乎設定TRANSLATOR可以實現上面的功能。

xxxx> select * from DBA_SQL_TRANSLATIONS
  2  @ prxx
==============================
OWNER                         : TTT
PROFILE_NAME                  : TEST_PROFILE
SQL_TEXT                      : select sysdate
TRANSLATED_TEXT               : select sysdate from dual
SQL_ID                        : bw2c1d6sqyjpy
HASH_VALUE                    : 2976859838
ENABLED                       : TRUE
REGISTRATION_TIME             : 2021-04-19 10:33:51.173270
CLIENT_INFO                   :
MODULE                        :
ACTION                        :
PARSING_USER_ID               :
PARSING_SCHEMA_ID             :
COMMENTS                      :
ERROR_CODE                    :
ERROR_SOURCE                  :
TRANSLATION_METHOD            :
DICTIONARY_SQL_ID             :
PL/SQL procedure successfully completed.

--//上網看了官方手冊，好複雜放棄。

4.收尾:
xxxx>  exec dbms_sql_translator.drop_profile(profile_name => 'test_profile');
PL/SQL procedure successfully completed.