Dominic Giles

A nice little feature in Oracle Database 12c is to query patching information via SQL. You can do this from SQLPlus or any other SQL interface jdbc/odbc etc. You can find more details here

http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_qopatch.htm#CBHEBGIB

However you won't be surprised to find that the following query doesn't currently return any useful information.

SYS@//oracle12c/orcl > select DBMS_QOPATCH.GET_OPATCH_LIST from dual;
GET_OPATCH_LIST
------------------------------------------------------------------------------------------------------------------------
<patches/>

A new feature in Oracle Database 12c is the ability to intercept and translate third party SQL to Oracle syntactically correct SQL  before it is parsed and executed. So you can now intercept SQL from applications using jdbc and odbc that were designed to run against a non Oracle database and potentially run them completely unchanged. The only work necessary is done by the database development/management team. In Oracle Database 12c we also currently support the automatic translation of some databases SQL. Currently this is limited to Sybase but we're working on others. You can find all the details here

http://docs.oracle.com/cd/E16655_01/gateways.121/e22508/sql_transl_arch.htm#DRDAA131

You can also use the frame work against an application that already successfully runs against an Oracle Database. You might want to do this for migration/performance/security reasons. It also gives you an opportunity to try out an important part of the framework "Translation Profiles".

The following SQL demonstrates a simple use case. I'm using the Swingbench Order Entry schema but the sample schema OE would work just as well.

First grant the privilege to the user you want to create the SQL profile on in this case SOE. You need to do this as sys or system

grant create sql translation profile to SOE

Then connect to the user you've just granted the privilege to (SOE) and create a SQL Translation profile.

-- Drop the profile if it already exists
-- exec DBMS_SQL_TRANSLATOR.DROP_PROFILE('ORDERS_APP_PROFILE');

-- Create a Translation Profile

exec dbms_sql_translator.create_profile('ORDERS_APP_PROFILE');

Then add some SQL to be translated. In our simple example we are translating a count against the ORDERS table and translating it to run against the ORDERS_SOUTH table

-- Create a Translation in that profile

BEGIN
    DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION(
      profile_name    => 'ORDERS_APP_PROFILE',
      sql_text        => 'select count(*) from orders',
      translated_text => 'select count(*) from orders_south');
END;

At this stage it's worth seeing whats been populated. You can see the SQL via the following views.

select *  FROM USER_SQL_TRANSLATION_PROFILES;

select * from USER_SQL_TRANSLATIONS;

Then test how this changes the execution by creating our new "ORDERS_SOUTH" table

-- Count the rows we get back from orders
select count(*) from orders;

-- Create a new table  orders_south with just ten rows in

create table orders_south as select * from orders where rownum < 11;

Now we've done that enable the sql translation profile we want to use

-- Set the session to use the sql translation profile

alter session set sql_translation_profile = ORDERS_APP_PROFILE

-- For testing make the sqlplus look like a foreign tool

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

Now when we re run our query it will use the ORDERS_SOUTH table even though we've explicitly asked for a count against the ORDERS table.

This blog is going production... Just like Oracle Database 12c.

 Comments and code snippets to follow