Fun with SQL Translation Framework

An interesting feature in 12c that is not talked about a lot is SQL Translation Framework. Kerry Osborne has an excellent blog post on this feature that should also be reviewed.

The advertised purpose of this software component is to allow statements written for another database to be translated into Oracle standard SQL. This will aid in migrations of programs from other database systems. In fact there is some relation with Oracle SQL Developer and its application scanner scripts in order to facilitate this sort of activity.

The SQL Translation Profile is a database object that contains the non-Oracle statements along with their translations. The profile can also change Oracle standard statements to different statements. I'll focus on the second feature of the component as it provides some interesting results.

Before parsing, the Translation framework will replace the text. There are some translations that are invalid, we'll look at one of those below as well.

To use this component the framework needs to be enabled. The session then needs to be altered to allow for the framework to be used. The steps are outlined as follows. A framework will be created and then access will be granted to a database  user, OP. After the setup is completed some translations will be created.

 exec dbms_sql_translator.create_profile('pj_test');

 grant all on sql translation profile pj_test to  op;

 alter session set sql_translation_profile=pj_test;
 alter system flush shared_pool;
 alter session set events = '10601 trace name context forever, level 32';


If there are any issues with the setup you will see messages like the following:
ORA-24252: SQL translation profile does not exist
24252. 00000 -  "SQL translation profile does not exist"
*Cause:    An attempt was made to access a SQL translation profile that
           either did not exist or for which the current user did not have
           privileges on.
*Action:   Check the SQL translation profile name and verify that the
           current user has appropriate privileges on the SQL translation
           profile.

You can change the statement slightly or you can access a totally different query by assigning a translation.

 begin
 dbms_sql_translator.register_sql_translation(
 profile_name => 'pj_test',
 sql_text => 'select 1 from op.vc',
 translated_text => 'select 42 from op.vc');
 end;
 /
 begin
 dbms_sql_translator.register_sql_translation(
 profile_name => 'pj_test',
 sql_text => 'select 2 from op.vc',
 translated_text => 'select * from op.dept');
 end;
 /

Now we can see the output
SQL> select 1 from op.vc;

        42
----------
        42

 1 rows selected 

SQL> select 2 from op.vc;

    DEPTNO DNAME          LOC         
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK     
        20 RESEARCH       DALLAS       
        30 SALES          CHICAGO      
        40 OPERATIONS     BOSTON       

 4 rows selected 

If I look in the data dictionary to see what has been run (factoring out some other unrelated queries) I see the following translations in place, but not the original statements (e.g. select 1 from op.vc).

SQL> select sql_text from v$sql where sql_text like 'select%from op.%';

SQL_TEXT                                                                       
--------------------------------------------------------------------------------
select * from op.dept                                                           
select 42 from op.vc 


You can imagine that this could be quite a security risk, if somebody were to translate a select statement into a delete/update/insert. Fortunately that type of translation is not allowed. For example, when I translate a select into a delete

 begin
 dbms_sql_translator.register_sql_translation(
 profile_name => 'pj_test',
 sql_text => 'select 3 from op.vc',
 translated_text => 'delete from op.dept where deptno=10');
 end;
 /

Running the statement gives an error.
select 3 from op.vc;

ORA-00900: invalid SQL statement
00900. 00000 -  "invalid SQL statement"
*Cause:    
*Action: