[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.

--//上网看了官方手册https://docs.oracle.com/database/121/ARPLS/d_sql_trans.htm#ARPLS73905，好复杂放弃。

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