12c Database : SQL Translation Framework – Developer thing

SQL Translation framework is a new feature in 12c to give ability to the developers to migrate code without changing the underlying code.

In addition , this feature also provides the translation of code from non-oracle to oracle coding.

Further more, this feature can be a enhancement to sql profiles baselines etc. which can be used for plan stability.

In order to work with this feature, you will need to work on below
1) Create a translation profile
2) Register sql statements that need to convert into the profile
3) Set the event 10601 to enable the profile to work

Quick look.

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

	PL/SQL procedure successfully completed.

	SQL> select object_name, object_type from dba_objects where object_name like 'TEST';

	OBJECT_NAME 				OBJECT_TYPE
	------------------------------------------------------------
	TEST        				SQL TRANSLATION PROFILE

	SQL> exec dbms_sql_translator.register_sql_translation('TEST','select count(*) from hr.countries','select count(*) from oe.countries');

	PL/SQL procedure successfully completed.

	SQL> grant all on sql translation profile TEST to hr,oe;

	Grant succeeded.

	SQL> alter session set sql_translation_profile = TEST;

	Session altered.

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

	Session altered.

	SQL> set echo on
	SQL> select count(*) from hr.countries;

	  COUNT(*)
	----------
		25

	SQL> select * from table(dbms_xplan.display_cursor());

	PLAN_TABLE_OUTPUT
	-----------------------------------------------------------
	SQL_ID  2yyznfag9cypc, child number 0
	-------------------------------------
	select count(*) from oe.countries

	Plan hash value: 1399856367

	----------------------------------------------------------------------------
	| Id  | Operation        | Name            | Rows  | Cost (%CPU)| Time     |
	----------------------------------------------------------------------------
	|   0 | SELECT STATEMENT |                 |       |     1 (100)|          |
	|   1 |  SORT AGGREGATE  |                 |     1 |            |          |
	|   2 |   INDEX FULL SCAN| COUNTRY_C_ID_PK |    25 |     1   (0)| 00:00:01 |
	----------------------------------------------------------------------------


	14 rows selected.

Did u observe the execution plan, it says oe.countries rather hr.countries. This feature is already there in MySQL and seems oracle listenting to all 🙂