SQL Translation Framework in Oracle Database 12c
By Okcan Yasin Saygili-Oracle on Nov 07, 2014
One of the new Oracle Database 12c features is the SQL Translation Framework. A key part of migrating non-Oracle databases to Oracle Database involves the conversion of non-Oracle SQL statements to SQL statements that are acceptable to Oracle Database. The conversion of the non-Oracle SQL statements of the applications is a manual and tedious process. To minimize the effort, or to eliminate the necessity for converting these statements, Oracle Database Release 12c introduces this new feature called SQL Translation Framework using which these SQL statements can be accepted from client applications and then can be translated at run-time before execution.
Although developed primarily to help customers migrate their 3rd party applications and databases to Oracle, by translating non-Oracle SQL statements, SQL Translation Framework can also be used to substitute an Oracle SQL statement with another Oracle statement to address a semantic or a performance issue, thus becoming an invaluable tool for application DBAs, who finally have the opportunity to substitute poorly performing SQL without the need change the application itself.
Database application migrations are assisted by Oracle SQL Developer and its application scanner scripts. SQL Developer can parse and document SQL statements that require translation before running against an Oracle Database. The task of doing the actual translations is left to the end user, or can be attempted one at a time using SQL Developer’s SQL Translation Scratch Editor, an ad hoc translation engine that allows users to connect to 3rd party databases, run their SQL statements, translate them to Oracle, run the statements again in Oracle Database, and compare the results. Developers can then manually update their applications to run the modified code.
This time consuming and error-prone process has been greatly enhanced with the introduction of the SQL Translation Framework in Oracle Database 12c, allowing for the SQL Translators, a software componenent, in Oracle SQL Developer to be loaded directly into the database as a collection of Java stored classes and procedures. Currently available with Oracle Database 12c are translators for Sybase ASE and SQL Server, but SQL Translators can also be provided by third-party vendors.
The SQL Translator can be engaged at either the session or service level. Programs can speak in their 'native' T-SQL dialect. The translator will translate the code on-the-fly, store them in a Translation Profile for later approval or editing, and the database will then execute the translated code. The Software Requirements for using SQL Translation Framework include Oracle Database 12c (All Editions but not XE as this is a Java feature) and SQL Developer 3.2.20_09 or later.
- Before using the SQL Translation framework , you must first install an SQL Translator in the Oracle Database.
- Then Create a SQL Translation Profile and register the SQL Translator with the profile.The SQL Translation Profile is a key component, as it's the database object that contains the set of captured non-Oracle SQL statements, and their translations or translation errors.
- Create a Database service and specify the SQL Translation Profile as a service attribute to which the application can connect.
- Link the application with an Oracle driver to connect the application to Oracle Database.
From now on, as the application runs, the SQL Translation Profile translates SQL statements of the application from the third-party SQL dialect to semantically-equivalent Oracle syntax and register them in the profile.Through the SQL Translation Profile we can review, approve, and modify translations. If the translator does not have a translation for a particular SQL statement or error, then you may register your own translation to fill its place.A profile is associated to a single translator. However, a translator can be used in one or more SQL Translation Profiles. Typically, there is one SQL Translation Profile per application, otherwise applications can share translated queries. You can export profiles among various databases.
Let's take a closer and see how to useSQL translation framework with below demo.
-- Create a Translation Profile
SQL> exec dbms_sql_translator.create_profile('deneme'); PL/SQL procedure successfully completed. SQL> select object_name, object_type from dba_objects where object_name like 'deneme'; OBJECT_NAME OBJECT_TYPE ------------------------------------------------------------ deneme SQL TRANSLATION PROFILE -- Add some SQL to be translated SQL> exec dbms_sql_translator.register_sql_translation('deneme','select count(*) from pm.employee','select count(*) from sh.employee'); PL/SQL procedure successfully completed.
You can see below side for plsql package (extend sample)
BEGIN DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION( profile_name => 'deneme', sql_text => 'select count(*) from pm.employee', translated_text => 'select count(*) from sh.employee'); END; SQL> grant all on sql translation profile deneme to pm,sh; Grant succeeded. SQL> alter session set sql_translation_profile = deneme; Session altered.-- Enable the profile to workSQL> alter session set events = '10601 trace name context forever, level 32'; Session altered.
This feature is designed to assist in the migration of 3rd party databases to Oracle Database. Migrating data and objects is a fairly straightforward procedure.
But what about the application code? This allows a developer to point their application to Oracle and to quickly see their application screens with the Oracle data coming back. Translations can be tweaked in the database instead of the application. Once all translations are complete and approved, the developers can begin planning on moving that code back into their application.
In the past , 3rd party 'foreign' SQL couldn't easily be converted to Oracle SQL, but now, after Oracle Database 12c, developers don't have to through a long process of manually figuring out how to tranform SQL Migration time can be greatly reduced with sql translation framework, thus, Oracle recommends SQL translation framework usage when needed, but also think of the potential this translation can provide you our Oracle Partners.
- Setting up SQL Translation Framework for Database 12cR1 - Oracle Learning Library OBE
- SQL Translation Framework Overview and Architecture - Oracle Database 12c Documentation
- Migrating Applications and Databases with Oracle Database 12c