Translating SQL (a migration tool)

When you are moving from another database to Oracle, in a perfect world, you’d set aside plenty of time to refactor all of your code and re-engineer your applications to get the maximum benefit out of the new database technology. But there are not many “perfect worlds” out there in the IT landscape Smile So sometimes we have to “make do” with the time and budget constraints that are placed upon us. To assist in migrations without having to change every piece of SQL code, you can use our translation tool that will intercept SQL as it is passed to the database, and replace with one of your own choosing.

Here’s a little demo of the feature called “SQL Translation”. First we’ll create a user DEMO that our “application” will be running under. It has two tables – a copy of EMP, and an empty copy of EMP (called EMP2)


SQL> create user demo identified by demo quota 100m on users;

User created.

SQL>
SQL> grant create session, alter session, create table, create sql translation profile, create procedure to DEMO;

Grant succeeded.

SQL>
SQL> create table demo.emp as select * from scott.emp;

Table created.

SQL>
SQL> create table demo.emp2 as select * from scott.emp where 1=0;

Table created.

Now we’ll create a translation profile that replaces a known query against EMP with an equivalent one against EMP2.



SQL> conn demo/demo
Connected.

SQL> exec DBMS_SQL_TRANSLATOR.DROP_PROFILE(profile_name    => 'DEMO_PROFILE'); exception when others then null;

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_sql_translator.create_profile('DEMO_PROFILE');

PL/SQL procedure successfully completed.

SQL> BEGIN
  2      DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION(
  3        profile_name    => 'DEMO_PROFILE',
  4        sql_text        => 'SELECT COUNT(*) FROM EMP WHERE DEPTNO = :B1', 
  5        translated_text => 'SELECT COUNT(*) FROM EMP2 WHERE DEPTNO = :B1');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> select *  from user_sql_translation_profiles
  2  @pr
==============================
PROFILE_NAME                  : DEMO_PROFILE
TRANSLATOR                    :
FOREIGN_SQL_SYNTAX            : TRUE
TRANSLATE_NEW_SQL             : TRUE
RAISE_TRANSLATION_ERROR       : FALSE
LOG_TRANSLATION_ERROR         : FALSE
TRACE_TRANSLATION             : FALSE

PL/SQL procedure successfully completed.


SQL> select * from user_sql_translations
  2  @pr
==============================
PROFILE_NAME                  : DEMO_PROFILE
SQL_TEXT                      : SELECT COUNT(*) FROM EMP WHERE DEPTNO = :B1
TRANSLATED_TEXT               : SELECT COUNT(*) FROM EMP2 WHERE DEPTNO = :B1
SQL_ID                        : ah8qd737hp24q
HASH_VALUE                    : 3473574038
ENABLED                       : TRUE
REGISTRATION_TIME             : 20-OCT-16 08.43.45.450000 AM
CLIENT_INFO                   :
MODULE                        :
ACTION                        :
PARSING_USER_ID               :
PARSING_SCHEMA_ID             :
COMMENTS                      :

PL/SQL procedure successfully completed.

We’ll flush out the shared pool (this is not necessary – it’s just to make seeing the results easier later)



SQL> conn / as sysdba
Connected.

SQL> alter system flush shared_pool;

System altered.

Now we’ll do a query without using the translation profile, just to show that the normal query works fine. There are 3 rows for DEPTNO=10 in our EMP table.

But then we will enable the translation profile, and run the same query again. This time, the profile will kick in, and the query will be replaced with our query to the EMP2 table (which is empty).


SQL> conn demo/demo
Connected.

SQL> variable B1 number
SQL> exec :B1 := 10

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM EMP WHERE DEPTNO = :B1;

  COUNT(*)
----------
         3

1 row selected.

SQL> alter session set sql_translation_profile = DEMO_PROFILE;

Session altered.

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

Session altered.

SQL>
SQL> SELECT COUNT(*) FROM EMP WHERE DEPTNO = :B1;

  COUNT(*)
----------
         0

1 row selected.

And there you go! The SQL got substituted without changing my “application”, and even support bind variables. There’s no hidden magic here – you can see both SQL’s in V$SQL.


SQL> conn / as sysdba
Connected.

SQL> select sql_id, sql_text from v$sql where sql_text like '%EMP%' and sql_text not like '%v$sql%';

SQL_ID        SQL_TEXT
------------- ----------------------------------------------------------------
ah8qd737hp24q SELECT COUNT(*) FROM EMP WHERE DEPTNO = :B1
9s91ww9a2d91c SELECT COUNT(*) FROM EMP2 WHERE DEPTNO = :B1

2 rows selected.

SQL>
SQL>

You can read more about the package here, and also see how it can integrated with SQL Developer here.

6 comments

nsk475gmailcom says:

October 20, 2016 at 12:29 pm

Hi connormcdonald,

always happy to learn from your posts and trying to understand the concepts.

In this scenario, am just trying to understand the context where it will be useful to us in our daily life?

Am still going through the package details, but before that wanted to check .

Many thanks,

1. connormcdonald says:
  
  October 20, 2016 at 1:01 pm
  
  You might have an app that was written to run against (say) sql server. You could use the translation facilities to run some or all of that app’s components against an Oracle database *without* changing the app.
  
  We provide translation “frameworks” as well, so its not as if you need to manually transpose every single SQL that might be run.
  
  1. nsk475gmailcom says:
    
    October 20, 2016 at 1:04 pm
    
    Ok assumption here is, as an example on sql server it might be using a table name as emp and oracle as emp2 and is it something that way?
    
    1. connormcdonald says:
      
      October 20, 2016 at 1:30 pm
      
      Yes, but of course, mine is a contrived example just to show the feature. A more realistic one might be something like:
      
      Source: select top 2 * from emp order by sal;
      
      Translation: select * from emp order by sal fetch first 2 rows only;
      
Dinesh s says:

October 27, 2016 at 11:05 pm

Hi
Will it be possible sql server dB to oracle dB by using sql translator?Correct me if I am wrong

jkstill says:

January 16, 2019 at 12:37 am

This is a tool that should be closely audited, as it is rather easily subverted to make unintended changes to data, even on SELECT statements.