SQL Transilation - Part I

SQL Transilation framework, a new mechanism available since Oracle 12c, ability to translate the SQL statements from the client program having non-oracle sql dialect into Oracle sql dialect that can be compiled by oracle compiler.

 

In addtion to translating non-oracle sql statements, SQL Transilation framework can also be used to substitute an Oracle sql statement with another oracle statement to address a performance issue. By this way we can address an application issue without patching client application.

 

Lets start explaining with a small example.

 

demo@XEPDB1> exec dbms_sql_translator.create_profile('MYDEMO');

 

PL/SQL procedure successfully completed.

 

demo@XEPDB1> begin

  2     dbms_sql_translator.register_sql_translation(

  3             profile_name=>'MYDEMO',

  4             sql_text=>'select * from emp',

  5             translated_text=>'select * from dept');

  6  end;

  7  /

 

PL/SQL procedure successfully completed.

 

Here is our first transilation profile, which transform all “ select * from emp” to “select * from dept”

 

When we turn on that sql transilation profile on this session and run “select * from emp” – nothing happens – however we are expecting this to be automatically transilated.

 

demo@XEPDB1> alter session set sql_translation_profile=MYDEMO;

 

Session altered.

 

demo@XEPDB1> select * from emp;

 

     EMPNO ENAME   JOB          MGR HIREDATE      SAL  COMM DEPTNO

---------- ------- --------- ------ ----------- ----- ----- ------

      7369 SMITH   CLERK       7902 17-DEC-1980   800           20

      7499 ALLEN   SALESMAN    7698 20-FEB-1981  1600   300     30

      7521 WARD    SALESMAN    7698 22-FEB-1981  1250   500     30

      7566 JONES   MANAGER     7839 02-APR-1981  2975           20

      7654 MARTIN  SALESMAN    7698 28-SEP-1981  1250  1400     30

      7698 BLAKE   MANAGER     7839 01-MAY-1981  2850           30

      7782 CLARK   MANAGER     7839 09-JUN-1981  2450           10

      7788 SCOTT   ANALYST     7566 19-APR-0087  3000           20

      7839 KING    PRESIDENT        17-NOV-1981  5000           10

      7844 TURNER  SALESMAN    7698 08-SEP-1981  1500     0     30

      7876 ADAMS   CLERK       7788 23-MAY-0087  1100           20

      7900 JAMES   CLERK       7698 03-DEC-1981   950           30

      7902 FORD    ANALYST     7566 03-DEC-1981  3000           20

      7934 MILLER  CLERK       7782 23-JAN-1982  1300           10

 

That is because the transilation feature is aimed at “foreign” sql being presented to the database from external tools and we convert it on the fly to an Oracle equivalent sql. Obviously SQLPlus is not a non-oracle or foreign source of sql, setting 10601 is our way of saying to the database, that this client tool (sql*plus) is not an Oracle one.

 

demo@XEPDB1> alter session set events ='10601 trace name context forever, level 32';

 

Session altered.

 

demo@XEPDB1> select * from emp;

 

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

 

When we set 10601 trace, we could see sql transilation in action.

 

The following enhancement comes with Oracle 18c, that is inside the API itself, we have the facility to tell the profile if that applies to foreign syntax or not and no more “10601” trace is needed.

 

demo@XEPDB1> alter session set sql_translation_profile=null;

 

Session altered.

 

demo@XEPDB1> conn demo/demo@pdb1

Connected.

demo@XEPDB1> exec dbms_sql_translator.drop_profile('DEMO_PROFILE');

 

PL/SQL procedure successfully completed.

 

demo@XEPDB1> begin

  2     dbms_sql_translator.create_profile('DEMO_PROFILE');

  3

  4     /* This attribute indicates if the profile is for traslation

  5             of foreign SQL syntax only. if True only foreign sql

  6             will be translated, if false, all sql from client

  7             application will be translated */

  8

  9     dbms_sql_translator.set_attribute(

 10             profile_name=>'DEMO_PROFILE',

 11             attribute_name=> dbms_sql_translator.ATTR_FOREIGN_SQL_SYNTAX,

 12             attribute_value=> dbms_sql_translator.ATTR_VALUE_FALSE );

 13

 14     dbms_sql_translator.register_sql_translation(

 15             profile_name=>'DEMO_PROFILE',

 16             sql_text=>'select * from emp',

 17             translated_text=>'select * from dept' );

 18  end;

 19  /

 

PL/SQL procedure successfully completed.

 

demo@XEPDB1> alter session set sql_translation_profile=DEMO_PROFILE;

 

Session altered.

 

demo@XEPDB1> select * from emp;

 

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

 

In the next blog post, will show how this can be applied for patching sql dynamically.

Proxy only connect

Yet another undocumented feature allows to define application schemas which can only be accessed through a proxy user, it makes a very usefull to assure that no user connects directly to application schema even by knowing its password.

 

Here is how it works:

 

demo@XEPDB1> create user app_user identified by "app_user";

 

User created.

 

demo@XEPDB1> grant create session to app_user;

 

Grant succeeded.

 

demo@XEPDB1> conn app_user/app_user@pdb1

Connected.

app_user@XEPDB1> show user

USER is "APP_USER"

 

demo@XEPDB1> create user my_user identified by "my_user";

 

User created.

 

demo@XEPDB1> grant create session to my_user;

 

Grant succeeded.

 

demo@XEPDB1> alter user app_user grant connect through my_user;

 

User altered.

 

demo@XEPDB1> conn my_user/my_user@pdb1

Connected.

my_user@XEPDB1> show user

USER is "MY_USER"

 

my_user@XEPDB1> conn my_user[app_user]/my_user@pdb1

Connected.

app_user@XEPDB1> show user

USER is "APP_USER"

app_user@XEPDB1> conn demo/demo@pdb1

Connected.

demo@XEPDB1> alter user app_user PROXY ONLY CONNECT;

 

User altered.

 

demo@XEPDB1> conn app_user/app_user@pdb1

ERROR:

ORA-28058: login is allowed only through a proxy

 

 

Warning: You are no longer connected to ORACLE.

demo@XEPDB1> conn my_user[app_user]/my_user@pdb1

Connected.

app_user@XEPDB1> show user

USER is "APP_USER"

app_user@XEPDB1> conn demo/demo@pdb1

Connected.

demo@XEPDB1> select username,proxy_only_connect

  2  from dba_users

  3  where username ='APP_USER';

 

USERNAME             P

-------------------- -

APP_USER             Y

 

demo@XEPDB1>

 

the syntax to revoke this change is:

 

demo@XEPDB1> alter user app_user cancel proxy only connect;

 

User altered.

 

demo@XEPDB1> select username,proxy_only_connect

  2  from dba_users

  3  where username ='APP_USER';

 

USERNAME             P

-------------------- -

APP_USER             N

 

demo@XEPDB1> conn app_user/app_user@pdb1

Connected.

app_user@XEPDB1> show user

USER is "APP_USER"

app_user@XEPDB1>