[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. --//上网看了官方手册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.