Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Rajeshwaran.

Asked: August 25, 2016 - 7:41 am UTC

Last updated: September 14, 2016 - 1:02 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Started reading about 12c SQL Translator from documentation
http://docs.oracle.com/database/121/DRDAA/sql_transl_arch.htm#CHDEIGFA

Here is a quick demo on that.
demo@ORA12C> create table t1 as select * from all_objects;

Table created.

demo@ORA12C> create table t2 as select * from all_objects;

Table created.

demo@ORA12C> create table t3 as select * from all_objects;

Table created.

demo@ORA12C>
demo@ORA12C> create index t1_idx on t1(owner,object_type,object_name);

Index created.

demo@ORA12C> create unique index t2_idx01 on t2(object_id);

Index created.

demo@ORA12C> create unique index t2_idx02 on t2(data_object_id);

Index created.

demo@ORA12C> create unique index t3_idx on t3(object_id);

Index created.

demo@ORA12C>
demo@ORA12C> create or replace procedure process_data(
  2     p1 int,
  3     p2 int,
  4     p3 int ) as
  5     l_value t1.object_name%type;
  6  begin
  7     select distinct t1.object_name
  8             into l_value
  9     from t1 left join t2
 10     on ( t1.owner = t2.owner and
 11             t1.object_type = t2.object_type and
 12             t1.object_name = t2.object_name)
 13     left join t3
 14     on ( t2.owner = t3.owner and
 15             t2.object_type = t3.object_type and
 16             t2.object_name = t3.object_name )
 17     where (t2.object_id = p1 or t3.object_id = p2)
 18     and t2.data_object_id = p3;
 19
 20     $if $$debug $then
 21             for x in (select * from table(dbms_xplan.display_cursor))
 22             loop
 23                     dbms_output.put_line(x.plan_table_output);
 24             end loop;
 25     $end
 26  end;
 27  /

Procedure created.

demo@ORA12C> alter procedure process_data compile plsql_ccflags ='debug:true';

Procedure altered.

demo@ORA12C> exec process_data(p1=>551, p2=>135, p3=>551);
SQL_ID  gcnz3hw7m4jxj, child number 0
-------------------------------------
SELECT DISTINCT T1.OBJECT_NAME FROM T1 LEFT JOIN T2 ON ( T1.OWNER =
T2.OWNER AND T1.OBJECT_TYPE = T2.OBJECT_TYPE AND T1.OBJECT_NAME =
T2.OBJECT_NAME) LEFT JOIN T3 ON ( T2.OWNER = T3.OWNER AND
T2.OBJECT_TYPE = T3.OBJECT_TYPE AND T2.OBJECT_NAME = T3.OBJECT_NAME )
WHERE (T2.OBJECT_ID = :B3 OR T3.OBJECT_ID = :B2 ) AND T2.DATA_OBJECT_ID
= :B1

Plan hash value: 3902587575

--------------------------------------------------------------------------------------------
| Id  | Operation                       | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |          |       |       |   459 (100)|          |
|   1 |  HASH UNIQUE                    |          |     1 |   135 |   459  (16)| 00:00:01 |
|   2 |   NESTED LOOPS SEMI             |          |     1 |   135 |   458  (16)| 00:00:01 |
|*  3 |    FILTER                       |          |       |       |            |          |
|   4 |     NESTED LOOPS OUTER          |          |     1 |    94 |   456  (16)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| T2       |     1 |    48 |     1   (0)| 00:00:01 |
|*  6 |       INDEX UNIQUE SCAN         | T2_IDX02 |     1 |       |     0   (0)|          |
|*  7 |      TABLE ACCESS FULL          | T3       |     1 |    46 |   455  (16)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN             | T1_IDX   | 81960 |  3281K|     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(("T2"."OBJECT_ID"=:B3 OR "T3"."OBJECT_ID"=:B2))
   6 - access("T2"."DATA_OBJECT_ID"=:B1)
   7 - filter(("T2"."OBJECT_NAME"="T3"."OBJECT_NAME" AND
              "T2"."OBJECT_TYPE"="T3"."OBJECT_TYPE" AND "T2"."OWNER"="T3"."OWNER"))
   8 - access("T1"."OWNER"="T2"."OWNER" AND "T1"."OBJECT_TYPE"="T2"."OBJECT_TYPE"
              AND "T1"."OBJECT_NAME"="T2"."OBJECT_NAME")

Note
-----
   - this is an adaptive plan


PL/SQL procedure successfully completed.

demo@ORA12C>

The AutoTrace shows this.
demo@ORA12C> variable b1 number
demo@ORA12C> variable b2 number
demo@ORA12C> variable b3 number
demo@ORA12C> exec :b1 := 551; :b2 := 135; :b3 :=551;

PL/SQL procedure successfully completed.

demo@ORA12C> set autotrace on explain statistics
demo@ORA12C> select distinct t1.object_name
  2  from t1 left join t2
  3  on ( t1.owner = t2.owner and
  4     t1.object_type = t2.object_type and
  5     t1.object_name = t2.object_name)
  6  left join t3
  7  on ( t2.owner = t3.owner and
  8     t2.object_type = t3.object_type and
  9     t2.object_name = t3.object_name )
 10  where (t2.object_id = to_number(:b1) or t3.object_id = to_number(:b2))
 11  and t2.data_object_id = to_number(:b3);

OBJECT_NAME
------------------------------
TSDP_SENSITIVE_DATA$

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3902587575

--------------------------------------------------------------------------------------------
| Id  | Operation                       | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |          |     1 |   135 |   459  (16)| 00:00:01 |
|   1 |  HASH UNIQUE                    |          |     1 |   135 |   459  (16)| 00:00:01 |
|   2 |   NESTED LOOPS SEMI             |          |     1 |   135 |   458  (16)| 00:00:01 |
|*  3 |    FILTER                       |          |       |       |            |          |
|   4 |     NESTED LOOPS OUTER          |          |     1 |    94 |   456  (16)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| T2       |     1 |    48 |     1   (0)| 00:00:01 |
|*  6 |       INDEX UNIQUE SCAN         | T2_IDX02 |     1 |       |     0   (0)| 00:00:01 |
|*  7 |      TABLE ACCESS FULL          | T3       |     1 |    46 |   455  (16)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN             | T1_IDX   | 81960 |  3281K|     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T2"."OBJECT_ID"=TO_NUMBER(:B1) OR "T3"."OBJECT_ID"=TO_NUMBER(:B2))
   6 - access("T2"."DATA_OBJECT_ID"=TO_NUMBER(:B3))
   7 - filter("T2"."OBJECT_NAME"="T3"."OBJECT_NAME"(+) AND
              "T2"."OBJECT_TYPE"="T3"."OBJECT_TYPE"(+) AND "T2"."OWNER"="T3"."OWNER"(+))
   8 - access("T1"."OWNER"="T2"."OWNER" AND "T1"."OBJECT_TYPE"="T2"."OBJECT_TYPE"
              AND "T1"."OBJECT_NAME"="T2"."OBJECT_NAME")

Note
-----
   - this is an adaptive plan


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1394  consistent gets
       1386  physical reads
          0  redo size
        563  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Table T3 has a unique index on OBJECT_ID, but that is not getting used here, and T3 is part of Outer joins.
So this new rewritten query is working fine and bench-marking results shows this.
demo@ORA12C> select distinct t1.object_name
  2  from t1 left join t2
  3  on ( t1.owner = t2.owner and
  4     t1.object_type = t2.object_type and
  5     t1.object_name = t2.object_name)
  6  left join t3
  7  on ( t2.owner = t3.owner and
  8     t2.object_type = t3.object_type and
  9     t2.object_name = t3.object_name and
 10     t3.object_id = to_number(:b2) )
 11  where (t2.object_id = to_number(:b1) and t2.data_object_id = to_number(:b3)) ;

OBJECT_NAME
------------------------------
TSDP_SENSITIVE_DATA$

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 512321169

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |     1 |   135 |     5  (20)| 00:00:01 |
|   1 |  HASH UNIQUE                   |          |     1 |   135 |     5  (20)| 00:00:01 |
|   2 |   NESTED LOOPS                 |          |     1 |   135 |     4   (0)| 00:00:01 |
|   3 |    NESTED LOOPS OUTER          |          |     1 |    94 |     2   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| T2       |     1 |    48 |     1   (0)| 00:00:01 |
|*  5 |      INDEX UNIQUE SCAN         | T2_IDX02 |     1 |       |     0   (0)| 00:00:01 |
|*  6 |     TABLE ACCESS BY INDEX ROWID| T3       |     1 |    46 |     1   (0)| 00:00:01 |
|*  7 |      INDEX UNIQUE SCAN         | T3_IDX   |     1 |       |     0   (0)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN            | T1_IDX   |     1 |    41 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("T2"."OBJECT_ID"=TO_NUMBER(:B1))
   5 - access("T2"."DATA_OBJECT_ID"=TO_NUMBER(:B3))
   6 - filter("T2"."OBJECT_NAME"="T3"."OBJECT_NAME"(+) AND
              "T2"."OWNER"="T3"."OWNER"(+) AND "T2"."OBJECT_TYPE"="T3"."OBJECT_TYPE"(+))
   7 - access("T3"."OBJECT_ID"(+)=TO_NUMBER(:B2))
   8 - access("T1"."OWNER"="T2"."OWNER" AND "T1"."OBJECT_TYPE"="T2"."OBJECT_TYPE"
              AND "T1"."OBJECT_NAME"="T2"."OBJECT_NAME")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        563  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

demo@ORA12C>

So instead of modifying the code inside the procedure, let me use SQL-Translator for query substitution.
demo@ORA12C> exec dbms_sql_translator.create_profile('FOO');

PL/SQL procedure successfully completed.

demo@ORA12C> column sql_text new_val sql
demo@ORA12C> select sql_text from v$sql where sql_id ='gcnz3hw7m4jxj';

SQL_TEXT
-------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT DISTINCT T1.OBJECT_NAME FROM T1 LEFT JOIN T2 ON ( T1.OWNER = T2.OWNER AND T1.OBJECT_TYPE = T2.OBJECT_TYPE AND T1.OBJECT_NAME = T2.OBJECT_NAME) L
EFT JOIN T3 ON ( T2.OWNER = T3.OWNER AND T2.OBJECT_TYPE = T3.OBJECT_TYPE AND T2.OBJECT_NAME = T3.OBJECT_NAME ) WHERE (T2.OBJECT_ID = :B3 OR T3.OBJECT_I
D = :B2 ) AND T2.DATA_OBJECT_ID = :B1


1 row selected.
demo@ORA12C> declare
  2     l_sql long;
  3  begin
  4     l_sql := q'| select distinct t1.object_name from t1 left join t2 on ( t1.owner = |' ||
  5     q'| t2.owner and  t1.object_type = t2.object_type and  t1.object_name = |' ||
  6     q'| t2.object_name) left join t3 on ( t2.owner = t3.owner and |' ||
  7     q'| t2.object_type = t3.object_type and  t2.object_name = t3.object_name |' ||
  8     q'| and  t3.object_id = :b2 ) where (t2.object_id = :b1 and |' ||
  9     q'| t2.data_object_id = :b3) |' ;
 10     dbms_sql_translator.register_sql_translation('FOO',
 11             sql_text=>'&sql',
 12             translated_text=>l_sql);
 13  end;
 14  /
old  11:                sql_text=>'&sql',
new  11:                sql_text=>'SELECT DISTINCT T1.OBJECT_NAME FROM T1 LEFT JOIN T2 ON ( T1.OWNER = T2.OWNER AND T1.OBJECT_TYPE = T2.OBJECT_TYPE AND T1.OBJECT_NAME = T2.OBJECT_NAME
) LEFT JOIN T3 ON ( T2.OWNER = T3.OWNER AND T2.OBJECT_TYPE = T3.OBJECT_TYPE AND T2.OBJECT_NAME = T3.OBJECT_NAME ) WHERE (T2.OBJECT_ID = :B3 OR T3.OBJECT_ID = :B2 ) AND T2.DATA_OBJECT_
ID = :B1 ',

PL/SQL procedure successfully completed.

So, took the code from shared pool and substituted with the alternative sql inside a SQL-Translation-Profile
demo@ORA12C> alter session set sql_translation_profile =foo;

Session altered.

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

Session altered.
demo@ORA12C> variable x clob
demo@ORA12C> exec dbms_sql_translator.translate_sql('&sql',:x);

PL/SQL procedure successfully completed.

demo@ORA12C> print x;

X
--------------------------------------------------------------------------------
 select distinct t1.object_name from t1 left join t2 on ( t1.owner =  t2.owner a
nd  t1.object_type = t2.object_type and  t1.object_name =  t2.object_name) left
join t3 on ( t2.owner = t3.owner and  t2.object_type = t3.object_type and  t2.ob
ject_name = t3.object_name  and  t3.object_id = :b2 ) where (t2.object_id = :b1
and  t2.data_object_id = :b3)

So the Translated SQL confirms that translation happens, but when executed the stored procedure, no changes to the plans, and still shows the old-sql's not the translated ones
demo@ORA12C> exec process_data(p1=>551, p2=>135, p3=>551);
SQL_ID  gcnz3hw7m4jxj, child number 0
-------------------------------------
SELECT DISTINCT T1.OBJECT_NAME FROM T1 LEFT JOIN T2 ON ( T1.OWNER =
T2.OWNER AND T1.OBJECT_TYPE = T2.OBJECT_TYPE AND T1.OBJECT_NAME =
T2.OBJECT_NAME) LEFT JOIN T3 ON ( T2.OWNER = T3.OWNER AND
T2.OBJECT_TYPE = T3.OBJECT_TYPE AND T2.OBJECT_NAME = T3.OBJECT_NAME )
WHERE (T2.OBJECT_ID = :B3 OR T3.OBJECT_ID = :B2 ) AND T2.DATA_OBJECT_ID
= :B1

Plan hash value: 3902587575

--------------------------------------------------------------------------------------------
| Id  | Operation                       | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |          |       |       |   459 (100)|          |
|   1 |  HASH UNIQUE                    |          |     1 |   135 |   459  (16)| 00:00:01 |
|   2 |   NESTED LOOPS SEMI             |          |     1 |   135 |   458  (16)| 00:00:01 |
|*  3 |    FILTER                       |          |       |       |            |          |
|   4 |     NESTED LOOPS OUTER          |          |     1 |    94 |   456  (16)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| T2       |     1 |    48 |     1   (0)| 00:00:01 |
|*  6 |       INDEX UNIQUE SCAN         | T2_IDX02 |     1 |       |     0   (0)|          |
|*  7 |      TABLE ACCESS FULL          | T3       |     1 |    46 |   455  (16)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN             | T1_IDX   | 81960 |  3281K|     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(("T2"."OBJECT_ID"=:B3 OR "T3"."OBJECT_ID"=:B2))
   6 - access("T2"."DATA_OBJECT_ID"=:B1)
   7 - filter(("T2"."OBJECT_NAME"="T3"."OBJECT_NAME" AND
              "T2"."OBJECT_TYPE"="T3"."OBJECT_TYPE" AND "T2"."OWNER"="T3"."OWNER"))
   8 - access("T1"."OWNER"="T2"."OWNER" AND "T1"."OBJECT_TYPE"="T2"."OBJECT_TYPE"
              AND "T1"."OBJECT_NAME"="T2"."OBJECT_NAME")

Note
-----
   - this is an adaptive plan


PL/SQL procedure successfully completed.

demo@ORA12C>


Questions:
1) Why no translation happen for the sql's inside the stored procedures ?
2) what is the purpose of this command "alter session set events = '10601 trace name context forever, level 32'" - sorry couldn't find that in documentation.

and Connor said...

I'll answer (2) first, because it will assist with (1)

2) The translation feature is aimed at "foreign" SQL being presented to the database from external tools, and we convert it on the fly to an Oracle-equivalent SQL. Well, obviously SQLPlus is not a non-Oracle or foreign source of SQL. Setting 10601 is our way of saying to the database ... "Hey, we're an external tool not really an Oracle one"

1) I would *suspect* the reason is similar to what I've said above. Anything being run in PLSQL must *by definition* be valid Oracle SQL, because it would not have compiled otherwise. So my hypothesis is that the translation facility is not even invoked in such cases. I'm seeking a confirmation for this.

As an aside, it's worth noting that SQL-from-PLSQL appears to have a trailing space. Here's my demo, and you can see - I need the trailing space to have the SQL_ID's line up correctly.

SQL> conn system/admin
Connected.

SQL> drop user demo cascade;

User dropped.

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

User created.

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

Grant succeeded.

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

Table created.

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

Table created.

SQL>
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> 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 ',   -- trailing space deliberate to align with PLSQL
  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                        : f42w70mf0pc7q
HASH_VALUE                    : 3691688182
ENABLED                       : TRUE
REGISTRATION_TIME             : 26-AUG-16 04.34.42.094000 PM
CLIENT_INFO                   :
MODULE                        :
ACTION                        :
PARSING_USER_ID               :
PARSING_SCHEMA_ID             :
COMMENTS                      :

PL/SQL procedure successfully completed.


SQL> conn system/admin
Connected.

SQL> alter system flush shared_pool;

System altered.

SQL> conn demo/demo
Connected.

SQL>
SQL> --
SQL> -- profile not set in session, result should be 3
SQL> --
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>
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> --
SQL> -- profile set in session, result should be 0
SQL> --
SQL>
SQL> SELECT COUNT(*) FROM EMP WHERE DEPTNO = :B1 ;

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

1 row selected.

SQL>
SQL> conn system/admin
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
------------- ----------------------------------------------------------------
9s91ww9a2d91c SELECT COUNT(*) FROM EMP2 WHERE DEPTNO = :B1
f42w70mf0pc7q SELECT COUNT(*) FROM EMP WHERE DEPTNO = :B1

2 rows selected.

SQL> conn demo/demo
Connected.

SQL> --
SQL> -- now same tests repeated within PLSQL
SQL> --
SQL>
SQL> create or replace
  2  procedure MY_PROC(p int) is
  3    l_result int;
  4  begin
  5    select count(*) into l_result from emp where deptno = p;
  6    dbms_output.put_line(l_result);
  7  end;
  8  /

Procedure created.

SQL> conn system/admin
Connected.

SQL> alter system flush shared_pool;

System altered.

SQL> conn demo/demo
Connected.

SQL>
SQL> --
SQL> -- profile not set in session, result should be 3
SQL> --

SQL> set serverout on
SQL> exec MY_PROC(10)
3

PL/SQL procedure successfully completed.

SQL>
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> -- profile set in session, result should be 0
SQL> --

SQL>
SQL> exec MY_PROC(10)
3

PL/SQL procedure successfully completed.

SQL>
SQL> conn system/admin
Connected.

SQL> --
SQL> -- query shows same SQL_ID as present in the translation definition, but no translation occurred
SQL> --
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
------------- ----------------------------------------------------------------
f42w70mf0pc7q SELECT COUNT(*) FROM EMP WHERE DEPTNO = :B1

1 row selected.

SQL>
SQL>


Rating

  (14 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

SQL Substitution

Rajeshwaran Jeyabal, August 26, 2016 - 12:03 pm UTC

The translation feature is aimed at "foreign" SQL being presented to the database from external tools, and we convert it on the fly to an Oracle-equivalent SQL.

Yes, in-addition to that translation, SQL-Translator also support SQL-Substitution. - Here is the quote from documentation, about this.

http://docs.oracle.com/database/121/DRDAA/sql_transl_arch.htm#DRDAA29133 
.....
In addition to translating non-Oracle SQL statements, the 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. 
In this way, you can address an application issue without patching the client application.
.....


As an aside, it's worth noting that SQL-from-PLSQL appears to have a trailing space. Here's my demo, and you can see - I need the trailing space to have the SQL_ID's line up correctly.

Since i loaded the SQL from the shared pool, do i need to explicitly specify this "Trailing Space" - while loading those sql from shared pool into dbms_sql_translator?

SQL> --
SQL> -- profile set in session, result should be 0
SQL> --

SQL>
SQL> exec MY_PROC(10)
3

PL/SQL procedure successfully completed.

SQL>
SQL> conn system/admin
Connected.

Sorry, i am unable to understand why this procedure returned "3" ? since the profile is set, it should be "0" right ?
Connor McDonald
August 26, 2016 - 1:25 pm UTC

Hence my comment

"So my hypothesis is that the translation facility is not even invoked in such cases. I'm seeking a confirmation for this."


Patching

Rajeshwaran Jeyabal, August 26, 2016 - 1:44 pm UTC

the 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.
In this way, you can address an application issue without patching the client application

So "substitute an "Oracle SQL" statement with another "Oracle statement" is not possible for SQL's inside the PL/SQL in the current database release (12.1.0.2) ? - Am i getting it correct?
Connor McDonald
August 27, 2016 - 2:09 am UTC

That is my *hypothesis*... I am seeking confirmation

any feasibility of checking with internal Teams

Rajeshwaran Jeyabal, August 28, 2016 - 4:13 am UTC

That is my *hypothesis*... I am seeking confirmation

Any possibility to check with the internal team, who build this functionality ?
Connor McDonald
August 30, 2016 - 2:46 am UTC

Confirmed. Not designed for use within plsql.

One obvious reason - think of the damage that could be done if you translated SQL's within Oracle-deliverd packages.

Using JAVA program Instead of PL/SQL

Rajeshwaran Jeyabal, August 28, 2016 - 8:00 am UTC

Anything being run in PLSQL must *by definition* be valid Oracle SQL, because it would not have compiled otherwise. So my hypothesis is that the translation facility is not even invoked in such cases. I'm seeking a confirmation for this.

Here is a demo using JAVA instead of PL/SQL ( create tables and create index are the same provided above)

The Java version is
D:\>java -version
java version "1.8.0_77"
Java(TM) SE Runtime Environment (build 1.8.0_77-b03)
Java HotSpot(TM) 64-Bit Server VM (build 25.77-b03, mixed mode)


MySqlBindVariableTest.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class MySqlBindVariableTest {
 public static void main(String[] args) {
  Connection conn = null;
  PreparedStatement prepStmt = null;
  ResultSet rs = null;
  String sqlStmt = null;
  try {
   Class.forName("oracle.jdbc.OracleDriver").newInstance();
   String connectionUrl ="jdbc:oracle:thin:@LT035221:1522:ORA12c";
   String connectionUser = "demo";
   String connectionPassword = "demo";
   conn = DriverManager.getConnection(connectionUrl, connectionUser, connectionPassword);
   //  sqlStmt="alter session set sql_translation_profile =foo";
   //  prepStmt = conn.prepareStatement(sqlStmt);
   //  rs = prepStmt.executeQuery();
   //  sqlStmt="alter session set events = '10601 trace name context forever, level 32'";
   //  prepStmt = conn.prepareStatement(sqlStmt); 
   //  rs = prepStmt.executeQuery();
   sqlStmt = "SELECT /* LOOK_FOR_ME */ distinct t1.object_name "+
    "from t1 left join t2 "+
    "on ( t1.owner = t2.owner and "+
    "  t1.object_type = t2.object_type and "+
    "  t1.object_name = t2.object_name) "+
    "left join t3 "+
    "on ( t2.owner = t3.owner and "+
    "  t2.object_type = t3.object_type and "+
    "  t2.object_name = t3.object_name ) "+
    "where (t2.object_id = ? or t3.object_id = ?) "+
    "and t2.data_object_id = ? ";
   System.out.println("SQL Statement:\n\t" + sqlStmt);
   prepStmt = conn.prepareStatement(sqlStmt);   
   prepStmt.setInt(1,551);
   prepStmt.setInt(2,135);
   prepStmt.setInt(3,551); 
   rs = prepStmt.executeQuery();
   while (rs.next()) {
    System.out.println(rs.getString(1));
   }
   prepStmt.close();
   System.out.println("O.K");
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   try {
    if (rs != null)
     rs.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
   try {
    if (prepStmt != null)
     prepStmt.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
   try {
    if (conn != null)
     conn.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }
}


Setting the Environmental variables
D:\>set path=%path%;C:\Program Files\Java\jdk1.8.0_77\bin

D:\>set classpath=%classpath%;D:\app\sqldeveloper\jdbc\lib\ojdbc6.jar;

D:\>set classpath=%classpath%;.;

D:\>


Before running the program, let me flush the shared pool.
rajesh@ORA12C>
rajesh@ORA12C> alter system flush shared_pool;

System altered.

rajesh@ORA12C> conn demo/demo@ora12c
Connected.
demo@ORA12C> column sql_text new_val sql


Ran the Java program here, from command prompt.
D:\>javac MySqlBindVariableTest.java

D:\>java MySqlBindVariableTest
SQL Statement:
        SELECT /* LOOK_FOR_ME */ distinct t1.object_name from t1 left join t2 on ( t1.owner = t2.owner and              t1.object_type = t2.object_type and             t1.object_name
= t2.object_name) left join t3 on ( t2.owner = t3.owner and             t2.object_type = t3.object_type and             t2.object_name = t3.object_name ) where (t2.object_id = ? or t3
.object_id = ?) and t2.data_object_id = ?
TSDP_SENSITIVE_DATA$
O.K


Then did this from database.

demo@ORA12C> column sql_text new_val sql
demo@ORA12C> select sql_text
  2  from v$sql
  3  where parsing_schema_name ='DEMO'
  4  and upper(sql_text) like 'SELECT /* LOOK_FOR_ME */%';

SQL_TEXT
-------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT /* LOOK_FOR_ME */ distinct t1.object_name from t1 left join t2 on ( t1.owner = t2.owner and   t1.object_type = t2.object_type and   t1.object_na
me = t2.object_name) left join t3 on ( t2.owner = t3.owner and   t2.object_type = t3.object_type and   t2.object_name = t3.object_name ) where (t2.obje
ct_id = :1  or t3.object_id = :2 ) and t2.data_object_id = :3


1 row selected.

demo@ORA12C> exec dbms_sql_translator.create_profile('FOO');

PL/SQL procedure successfully completed.

demo@ORA12C> declare
  2     l_sql long;
  3  begin
  4     l_sql := q'| select distinct t1.object_name from t1 left join t2 on ( t1.owner = |' ||
  5     q'| t2.owner and  t1.object_type = t2.object_type and  t1.object_name = |' ||
  6     q'| t2.object_name) left join t3 on ( t2.owner = t3.owner and |' ||
  7     q'| t2.object_type = t3.object_type and  t2.object_name = t3.object_name |' ||
  8     q'| and  t3.object_id = :b2 ) where (t2.object_id = :b1 and |' ||
  9     q'| t2.data_object_id = :b3) |' ;
 10     dbms_sql_translator.register_sql_translation('FOO',
 11             sql_text=>'&sql',
 12             translated_text=>l_sql);
 13  end;
 14  /
old  11:                sql_text=>'&sql',
new  11:                sql_text=>'SELECT /* LOOK_FOR_ME */ distinct t1.object_name from t1 left join t2 on ( t1.owner = t2.owner and   t1.object_type = t2.object_type and   t1.object
_name = t2.object_name) left join t3 on ( t2.owner = t3.owner and   t2.object_type = t3.object_type and  t2.object_name = t3.object_name ) where (t2.object_id = :1  or t3.object_id =
:2 ) and t2.data_object_id = :3 ',

PL/SQL procedure successfully completed.

demo@ORA12C> alter session set sql_translation_profile=FOO;

Session altered.

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

Session altered.

demo@ORA12C> variable x clob
demo@ORA12C> exec dbms_sql_translator.translate_sql('&sql',:x);

PL/SQL procedure successfully completed.

demo@ORA12C> print x;

X
--------------------------------------------------------------------------------
 select distinct t1.object_name from t1 left join t2 on ( t1.owner =  t2.owner a
nd  t1.object_type = t2.object_type and  t1.object_name =  t2.object_name) left
join t3 on ( t2.owner = t3.owner and  t2.object_type = t3.object_type and  t2.ob
ject_name = t3.object_name  and  t3.object_id = :b2 ) where (t2.object_id = :b1
and  t2.data_object_id = :b3)


so far every thing looks good (able to see the new sql getting translated here).

so let me flush the shared pool again.

demo@ORA12C> CONN rajesh/oracle@ora12c
Connected.
rajesh@ORA12C> alter system flush shared_pool ;

System altered.

rajesh@ORA12C> conn demo/demo@ora12c
Connected.
demo@ORA12C> select sql_text
  2  from v$sql
  3  where parsing_schema_name ='DEMO'
  4  and upper(sql_text) like 'SELECT /* LOOK_FOR_ME */%' ;

no rows selected


now, go back to Java code (MySqlBindVariableTest.java) and uncomment all the single line comments("//") from line no# 19 to 24 and then compile and run this program.

D:\>javac MySqlBindVariableTest.java

D:\>java MySqlBindVariableTest
SQL Statement:
        SELECT /* LOOK_FOR_ME */ distinct t1.object_name from t1 left join t2 on ( t1.owner = t2.owner and              t1.object_type = t2.object_type and             t1.object_name
= t2.object_name) left join t3 on ( t2.owner = t3.owner and             t2.object_type = t3.object_type and             t2.object_name = t3.object_name ) where (t2.object_id = ? or t3
.object_id = ?) and t2.data_object_id = ?
TSDP_SENSITIVE_DATA$
O.K


Now, went back to the database, to verify them(after the Java execution), it shows this.
demo@ORA12C> select sql_text
  2  from v$sql
  3  where parsing_schema_name ='DEMO' ;

SQL_TEXT
-------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT /* LOOK_FOR_ME */ distinct t1.object_name from t1 left join t2 on ( t1.owner = t2.owner and   t1.object_type = t2.object_type and   t1.object_na
me = t2.object_name) left join t3 on ( t2.owner = t3.owner and   t2.object_type = t3.object_type and   t2.object_name = t3.object_name ) where (t2.obje
ct_id = :1  or t3.object_id = :2 ) and t2.data_object_id = :3

select sql_text from v$sql where parsing_schema_name ='DEMO'

2 rows selected.

demo@ORA12C>


Still don't find the Translated SQL in the shared pool for those SQL got executed from JAVA program, is that i am missing something here ?
Connor McDonald
August 30, 2016 - 2:45 am UTC

I think you are not getting the SQL to match. Here's my example

I changed your program to be as per my initial example:

sqlStmt = "SELECT COUNT(*) FROM EMP WHERE DEPTNO = ?";

Notice - *no* trailing space in the *java* code.

And here my database initialisation

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> 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 = :1 ',   -- 1 trailing space
  5        translated_text => 'SELECT COUNT(*) FROM EMP2 WHERE DEPTNO = :1');
  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 = :1
TRANSLATED_TEXT               : SELECT COUNT(*) FROM EMP2 WHERE DEPTNO = :1
SQL_ID                        : dcfu2rmf00qd0
HASH_VALUE                    : 3691010464
ENABLED                       : TRUE
REGISTRATION_TIME             : 30-AUG-16 10.36.08.033000 AM
CLIENT_INFO                   :
MODULE                        :
ACTION                        :
PARSING_USER_ID               :
PARSING_SCHEMA_ID             :
COMMENTS                      :

PL/SQL procedure successfully completed.

SQL> alter system flush shared_pool;

System altered.


Notice - *1* trailing space in the *profile setup* code.

Now I compiled/ran the java program and got the expected 3 rows

C:\temp>java MySqlBindVariableTest
SQL Statement:
        SELECT COUNT(*) FROM EMP WHERE DEPTNO = ?
3
O.K

SQL> select sql_id, sql_text from v$sql
  2  where sql_text like '%WHERE DEPTNO%';

SQL_ID        SQL_TEXT
------------- ----------------------------------------------------------------
dcfu2rmf00qd0 SELECT COUNT(*) FROM EMP WHERE DEPTNO = :1
8k84p2jb9b1v2 select sql_id, sql_text from v$sql where sql_text like '%WHERE D
              EPTNO%'


Important note: You can see that the SQL_ID found in v$sql is a *match* for the one that I have created the translation profile for.

So now I edited the java program, and commented back in:

sqlStmt="alter session set sql_translation_profile =DEMO_PROFILE";
prepStmt = conn.prepareStatement(sqlStmt);
rs = prepStmt.executeQuery();
sqlStmt="alter session set events = '10601 trace name context forever, level 32'";
prepStmt = conn.prepareStatement(sqlStmt);
rs = prepStmt.executeQuery();

and here's the results (shared pool was flushed again)

C:\temp>java MySqlBindVariableTest
SQL Statement:
        SELECT COUNT(*) FROM EMP WHERE DEPTNO = ?
0
O.K


SQL> select sql_id, sql_text from v$sql
  2  where sql_text like '%WHERE DEPTNO%';

SQL_ID        SQL_TEXT
------------- ----------------------------------------------------------------
96k8ryxhram8q SELECT COUNT(*) FROM EMP2 WHERE DEPTNO = :1
8k84p2jb9b1v2 select sql_id, sql_text from v$sql where sql_text like '%WHERE D
              EPTNO%'


You can see the translation was done, and we (correctly) got no rows.

Trailing space

Rajeshwaran Jeyabal, August 30, 2016 - 4:11 am UTC

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

Thanks for the details provided.

Could you please help me to get these.

1) What is the need for this "Trailing space" - don't find that mentioned in the documentation
http://docs.oracle.com/database/121/DRDAA/sqltran.htm#DRDAA29115
2) from my demo, i copied the SQL from the Shared_Pool - so still we do need to introduce this "Trailing space" while profile creation ?
3) Is this "Trailing Space" is need for SQL getting executed from .Net code base ?
Connor McDonald
August 31, 2016 - 1:00 am UTC

The trailing space is nothing special to the translation framework, it's just what I needed to ensure matching was occuring. Example:

SQL> declare
  2    x int;
  3    y int;
  4  begin
  5    select count(*) into x from all_users where user_id = y;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> select sql_id, 'x'||sql_text||'x'
  2  from v$sql
  3  where sql_text like '%ALL_USERS%'
  4  and sql_text not like '%sql%';

SQL_ID
-------------
'X'||SQL_TEXT||'X'
------------------------------------------------------------
ayd7gwk0296dt
xSELECT COUNT(*) FROM ALL_USERS WHERE USER_ID = :B1 x


1 row selected.

SQL> select dump(sql_text) from v$sql where sql_id = 'ayd7gwk0296dt';

DUMP(SQL_TEXT)
------------------------------------------------------------
Typ=1 Len=51: 83,69,76,69,67,84,32,67,79,85,78,84,40,42,41,3
2,70,82,79,77,32,65,76,76,95,85,83,69,82,83,32,87,72,69,82,6
9,32,85,83,69,82,95,73,68,32,61,32,58,66,49,32


1 row selected.


I dont know *why* we get the trailing chr(32)...but we do, so I accounted for it in the translation profile.

Thanks ! - Able to get it now.

Rajeshwaran Jeyabal, August 31, 2016 - 6:17 am UTC

Thanks Team - I am able to get "SQL-Translation" working now.

Here is my working demo.

MySqlBindVariableTest.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class MySqlBindVariableTest {
 public static void main(String[] args) {
  Connection conn = null;
  PreparedStatement prepStmt = null;
  ResultSet rs = null;
  String sqlStmt = null;
  try {
   Class.forName("oracle.jdbc.OracleDriver").newInstance();
   String connectionUrl ="jdbc:oracle:thin:@LT035221:1522:ORA12c";
   String connectionUser = "demo";
   String connectionPassword = "demo";
   conn = DriverManager.getConnection(connectionUrl, connectionUser, connectionPassword);
    // sqlStmt="alter session set sql_translation_profile =FOO";
    // prepStmt = conn.prepareStatement(sqlStmt);
    // rs = prepStmt.executeQuery();
    // sqlStmt="alter session set events = '10601 trace name context forever, level 32'";
    // prepStmt = conn.prepareStatement(sqlStmt); 
    // rs = prepStmt.executeQuery();
   sqlStmt = "SELECT /* LOOK_FOR_ME */ distinct t1.object_name"+
    " from t1 left join t2"+
    " on(t1.owner=t2.owner and"+
    " t1.object_type=t2.object_type and"+
    " t1.object_name=t2.object_name)"+
    " left join t3"+
    " on(t2.owner=t3.owner and"+
    " t2.object_type=t3.object_type and"+
    " t2.object_name = t3.object_name)"+
    " where (t2.object_id=? or t3.object_id=?)"+
    " and t2.data_object_id=?";
   System.out.println("SQL Statement:\n\t" + sqlStmt);
   prepStmt = conn.prepareStatement(sqlStmt);   
   prepStmt.setInt(1,551);
   prepStmt.setInt(2,135);
   prepStmt.setInt(3,551); 
   rs = prepStmt.executeQuery();
   while (rs.next()) {
    System.out.println(rs.getString(1));
   }
   prepStmt.close();
   System.out.println("O.K");
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   try {
    if (rs != null)
     rs.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
   try {
    if (prepStmt != null)
     prepStmt.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
   try {
    if (conn != null)
     conn.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }
}


Connected to the database, to verify the content in the Shared pool.
demo@ORA12C> column sql_text new_val sql
demo@ORA12C> select sql_text
  2  from v$sql
  3  where parsing_schema_name ='DEMO'
  4  and upper(sql_text) like 'SELECT /* LOOK_FOR_ME */%';

no rows selected

demo@ORA12C>
demo@ORA12C>


Compiled and ran the Java program
D:\>javac MySqlBindVariableTest.java

D:\>java MySqlBindVariableTest
SQL Statement:
        SELECT /* LOOK_FOR_ME */ distinct t1.object_name from t1 left join t2 on(t1.owner=t2.owner and t1.object_type=t2.object_type and t1.object_name=t2.object_name) left join t3 on
(t2.owner=t3.owner and t2.object_type=t3.object_type and t2.object_name = t3.object_name) where (t2.object_id=? or t3.object_id=?) and t2.data_object_id=?
TSDP_SENSITIVE_DATA$
O.K

Went back to the database to verify the shared pool contents.
demo@ORA12C>
demo@ORA12C> select sql_text
  2  from v$sql
  3  where parsing_schema_name ='DEMO'
  4  and upper(sql_text) like 'SELECT /* LOOK_FOR_ME */%';

SQL_TEXT
-------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT /* LOOK_FOR_ME */ distinct t1.object_name from t1 left join t2 on(t1.owner=t2.owner and t1.object_type=t2.object_type and t1.object_name=t2.obje
ct_name) left join t3 on(t2.owner=t3.owner and t2.object_type=t3.object_type and t2.object_name = t3.object_name) where (t2.object_id=:1  or t3.object_
id=:2 ) and t2.data_object_id=:3


1 row selected.

Yes, the "Trailing" space is available with the SQL.
demo@ORA12C> column x format a40
demo@ORA12C> select '"'||sql_text||'"' x
  2  from v$sql
  3  where parsing_schema_name ='DEMO'
  4  and upper(sql_text) like 'SELECT /* LOOK_FOR_ME */%';

X
----------------------------------------
"SELECT /* LOOK_FOR_ME */ distinct t1.ob
ject_name from t1 left join t2 on(t1.own
er=t2.owner and t1.object_type=t2.object
_type and t1.object_name=t2.object_name)
 left join t3 on(t2.owner=t3.owner and t
2.object_type=t3.object_type and t2.obje
ct_name = t3.object_name) where (t2.obje
ct_id=:1  or t3.object_id=:2 ) and t2.da
ta_object_id=:3 "


1 row selected.


Now, created the sql-translation profile and "seed" the sql from shared-pool into this profile.

demo@ORA12C> exec dbms_sql_translator.create_profile('FOO');

PL/SQL procedure successfully completed.

demo@ORA12C>
demo@ORA12C> declare
  2     l_sql long;
  3  begin
  4     l_sql := q'| select distinct t1.object_name from t1 left join t2 on ( t1.owner = |' ||
  5     q'| t2.owner and  t1.object_type = t2.object_type and  t1.object_name = |' ||
  6     q'| t2.object_name) left join t3 on ( t2.owner = t3.owner and |' ||
  7     q'| t2.object_type = t3.object_type and  t2.object_name = t3.object_name |' ||
  8     q'| and  t3.object_id = :b2 ) where (t2.object_id = :b1 and |' ||
  9     q'| t2.data_object_id = :b3) |' ;
 10     dbms_sql_translator.register_sql_translation('FOO',
 11             sql_text=>'&sql',
 12             translated_text=>l_sql);
 13  end;
 14  /
old  11:            sql_text=>'&sql',
new  11:            sql_text=>'SELECT /* LOOK_FOR_ME */ distinct t1.object_name from t1 left join t2 on(t1.owner=t2.owner and t1.object_type=t2.object_type and t1.object_name=t2.objec
t_name) left join t3 on(t2.owner=t3.owner and t2.object_type=t3.object_type and t2.object_name = t3.object_name) where (t2.object_id=:1 or t3.object_id=:2 ) and t2.data_object_id=:3 '
,

PL/SQL procedure successfully completed.

demo@ORA12C> alter session set sql_translation_profile=FOO;

Session altered.

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

Session altered.

demo@ORA12C> variable x clob
demo@ORA12C> exec dbms_sql_translator.translate_sql('&sql',:x);

PL/SQL procedure successfully completed.

demo@ORA12C> print x

X
----------------------------------------
 select distinct t1.object_name from t1
left join t2 on ( t1.owner =  t2.owner a
nd  t1.object_type = t2.object_type and
 t1.object_name =  t2.object_name) left
join t3 on ( t2.owner = t3.owner and  t2
.object_type = t3.object_type and  t2.ob
ject_name = t3.object_name  and  t3.obje
ct_id = :b2 ) where (t2.object_id = :b1
and  t2.data_object_id = :b3)


So far everything is good, flushed the shared pool, compiled and ran the java program(this time with line no#19 to line no#24 got un-commented) again.

D:\>javac MySqlBindVariableTest.java

D:\>java MySqlBindVariableTest
SQL Statement:
        SELECT /* LOOK_FOR_ME */ distinct t1.object_name from t1 left join t2 on(t1.owner=t2.owner and t1.object_type=t2.object_type and t1.object_name=t2.object_name) left join t3 on
(t2.owner=t3.owner and t2.object_type=t3.object_type and t2.object_name = t3.object_name) where (t2.object_id=? or t3.object_id=?) and t2.data_object_id=?
O.K

D:\>


Went back to the database, now the Translated SQL exists in the Shared pool.

demo@ORA12C> conn rajesh/oracle@ora12c
Connected.
rajesh@ORA12C> select sql_text
  2  from v$sql
  3  where parsing_schema_name ='DEMO' ;

SQL_TEXT
-------------------------------------------------------------------------------------------------------------------------------------------------------
 select distinct t1.object_name from t1 left join t2 on ( t1.owner =  t2.owner and  t1.object_type = t2.object_type and  t1.object_name =  t2.object_na
me) left join t3 on ( t2.owner = t3.owner and  t2.object_type = t3.object_type and  t2.object_name = t3.object_name  and  t3.object_id = :b2 ) where (t
2.object_id = :b1 and  t2.data_object_id = :b3)


1 row selected.

rajesh@ORA12C>



Thank You !
Connor McDonald
September 01, 2016 - 2:26 am UTC

Nice work!


Java pool Size

Rajeshwaran Jeyabal, August 31, 2016 - 1:52 pm UTC

http://docs.oracle.com/database/121/DRDAA/sql_transl_install.htm#DRDAA29156

Now, working on a demo for MSSQL to Oracle SQL using SQL Traslators and got this error.

demo@ORA12C> select top 3 * from emp ;
select top 3 * from emp
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of TRANSLUSER./ca68845b_SQLServerTSQLTransla
ORA-04031: unable to allocate 4096 bytes of shared memory ("java pool","oracle/sql/TypeDescriptor","JOXLE^84bab6ee",":SGAClass")
ORA-06512: at "TRANSLUSER.SQLSERVER_TSQL_TRANSLATOR", line 3
ORA-06512: at line 1


demo@ORA12C>


Here is the details on SGA.
rajesh@ORA12C> select * from v$sga ;

NAME                                                    VALUE     CON_ID
-------------------------------------------------- ---------- ----------
Fixed Size                                            3050120          0
Variable Size                                       557843832          0
Database Buffers                                     20971520          0
Redo Buffers                                          5337088          0
In-Memory Area                                      159383552          0

5 rows selected.

rajesh@ORA12C> show parameter java_pool

NAME                                          TYPE        VALUE
--------------------------------------------- ----------- -------------------------
java_pool_size                                big integer 0
rajesh@ORA12C>
rajesh@ORA12C> show parameter memory_target

NAME                                          TYPE        VALUE
--------------------------------------------- ----------- -------------------------
memory_target                                 big integer 712M
rajesh@ORA12C>


I have set the memory target, do i still need to explicitly set "Java_pool_size" for this ? if so what would be the optimal value for this?
Connor McDonald
September 02, 2016 - 1:54 am UTC

What does

select * from v$sgastat where pool = 'java pool';

return

Java pool size

Rajeshwaran Jeyabal, September 02, 2016 - 2:38 am UTC 

demo@ORA12C> select * from v$sgastat where pool = 'java pool';

POOL         NAME                                                    BYTES     CON_ID
------------ -------------------------------------------------- ---------- ----------
java pool    free memory                                            836480          0
java pool    joxs heap                                              459648          0
java pool    KGH: NO ACCESS                                          20480          0
java pool    JOXLE                                                40626432          0

4 rows selected.

demo@ORA12C>

Connor McDonald
September 06, 2016 - 2:31 am UTC

So it looks like we bumped up the java pool to ~40M and couldnt go any further. It could be that we couldnt steal any more memory from the other pools, or it could be a bug. Perhaps check the "... RESIZE_OPS" views to see what reshuffles took place if any.

If this is a 'play' database, I'd crank up memory_target, and perhaps also set a lower limit on java pool size and see how you go.

If you still get the error, ie, the java pool keeps getting exhausted, I'd suspect you've hit a bug and check with Support.

ORA-29541: Error

Rajeshwaran Jeyabal, September 06, 2016 - 10:14 am UTC 

rajesh@ORA12C> create user transluser identified by transluser;

User created.

rajesh@ORA12C> grant connect,resource,create view,DBA to transluser;

Grant succeeded.

rajesh@ORA12C> alter user transluser quota unlimited on data_12c_noncdb;

User altered.

rajesh@ORA12C> alter user transluser quota unlimited on system;

User altered.

rajesh@ORA12C> grant create sql translation profile to transluser;

Grant succeeded.

rajesh@ORA12C> begin
  2     dbms_java.grant_permission('TRANSLUSER', 'SYS:java.lang.RuntimePermission', 'getClassLoader', '');
  3     dbms_java.grant_permission('TRANSLUSER', 'SYS:java.lang.RuntimePermission', 'setContextClassLoader', '');
  4  end;
  5  /

PL/SQL procedure successfully completed.


Then went into SQL Developer 4.1.3, navigated to , view -> DBA connections , added this connection "TRANSLUSER" to DBA connection panel.

opened "SQL Translation Framework", right clicked "SQL Translators" and then choose "Install SQL Translator"

select the mode as "online" and check the "create new profile" option and provide profile name as "MSSQL_PROFILE" and profile schema as "TRANSLUSER" from the drop down.

Then back in sql*plus did this.

rajesh@ORA12C> conn transluser/transluser@ora12c
Connected.
transluser@ORA12C> grant execute on sqlserver_tsql_translator to demo;

Grant succeeded.

transluser@ORA12C> conn demo/demo@ora12c
Connected.
demo@ORA12C>
demo@ORA12C> select * from session_privs
  2  where privilege='CREATE SQL TRANSLATION PROFILE';

PRIVILEGE
----------------------------------------
CREATE SQL TRANSLATION PROFILE

1 row selected.
demo@ORA12C> exec dbms_sql_translator.create_profile('MSSQL_PROFILE');

PL/SQL procedure successfully completed.

demo@ORA12C> begin
  2     dbms_sql_translator.set_attribute(
  3             profile_name=>'MSSQL_PROFILE',
  4             attribute_name=>dbms_sql_translator.ATTR_TRANSLATOR,
  5             attribute_value=>'transluser.sqlserver_tsql_translator');
  6  end;
  7  /

PL/SQL procedure successfully completed.

demo@ORA12C> select top 3 * from emp order by empno;
select top 3 * from emp order by empno
           *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


demo@ORA12C> alter session set sql_translation_profile=MSSQL_PROFILE;

Session altered.

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

Session altered.

demo@ORA12C> select top 3 * from emp order by empno;
select top 3 * from emp order by empno
*
ERROR at line 1:
ORA-29541: class TRANSLUSER.oracle/dbtools/babelfish/sqlserver/SQLServerTSQLTranslator could not be resolved
ORA-06512: at "TRANSLUSER.SQLSERVER_TSQL_TRANSLATOR", line 3
ORA-06512: at "TRANSLUSER.SQLSERVER_TSQL_TRANSLATOR", line 17
ORA-06512: at line 1


demo@ORA12C>


could you help us on, how this error "ORA-29541: class TRANSLUSER.oracle/dbtools/babelfish/sqlserver/SQLServerTSQLTranslator could not be resolved" could be resolved ?
Connor McDonald
September 09, 2016 - 8:18 am UTC

Does the translation work if you are connected as transluser ?

(I've been following the instructions in

https://docs.oracle.com/database/121/DRDAA/sql_transl_install.htm#DRDAA29148

and it works OK for me, but of course, I'm running queries as the same connection that the translater was installed into. So lets see if we can get that running for you first.

ORA-29541: Error | Transluser

Rajeshwaran Jeyabal, September 12, 2016 - 11:03 am UTC

The same error from Transluser

demo@ORA12C> conn transluser/transluser@ora12c
Connected.
transluser@ORA12C> 
transluser@ORA12C> exec dbms_sql_translator.drop_profile('MSSQL_PROFILE');

PL/SQL procedure successfully completed.

transluser@ORA12C> exec dbms_sql_translator.create_profile('MSSQL_PROFILE');

PL/SQL procedure successfully completed.

transluser@ORA12C> begin
  2   dbms_sql_translator.set_attribute(
  3              profile_name=>'MSSQL_PROFILE',
  4              attribute_name=>dbms_sql_translator.ATTR_TRANSLATOR,
  5              attribute_value=>'transluser.sqlserver_tsql_translator');
  6  end;
  7  /

PL/SQL procedure successfully completed.

transluser@ORA12C> select top 3 * from emp order by empno;
select top 3 * from emp order by empno
           *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


transluser@ORA12C> alter session set sql_translation_profile=MSSQL_PROFILE;

Session altered.

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

Session altered.

transluser@ORA12C> select top 3 * from emp order by empno;
select top 3 * from emp order by empno
*
ERROR at line 1:
ORA-29541: class TRANSLUSER.oracle/dbtools/babelfish/sqlserver/SQLServerTSQLTranslator could not be resolved
ORA-06512: at "TRANSLUSER.SQLSERVER_TSQL_TRANSLATOR", line 3
ORA-06512: at "TRANSLUSER.SQLSERVER_TSQL_TRANSLATOR", line 17
ORA-06512: at line 1


transluser@ORA12C>

Connor McDonald
September 13, 2016 - 2:26 am UTC

That sounds like the java stuff wasn't correctly loaded. Compare your catalog with below

SQL> select object_name, object_type, dbms_java.longname(object_name) lname
  2  from   dba_objects
  3  where  owner = 'MCDONAC'
  4  and    object_type like 'JAVA%'
  5  order by 2,1

OBJECT_NAME                              OBJECT_TYPE             LNAME
---------------------------------------- ----------------------- ------------------------------------------------------------
/100f95a5_JTreeStringTemplateM           JAVA CLASS              org/antlr/stringtemplate/misc/JTreeStringTemplateModel$ListW
/101b793_MigrationProjectNavig           JAVA CLASS              oracle/dbtools/migration/workbench/core/navigator/MigrationP
/10535032_SQLServerBrowserAddi           JAVA CLASS              oracle/dbtools/thirdparty/sqlserver/SQLServerBrowserAddin
/1069bf51_ElementAttributes              JAVA CLASS              oracle/ide/model/ElementAttributes
/10806132_DataMoveWorker                 JAVA CLASS              oracle/dbtools/migration/datamove/online/DataMoveWorker
/1081b926_MigrationLogFormatte           JAVA CLASS              oracle/dbtools/migration/workbench/core/navigator/MigrationL
...
...
...
2813 rows selected.


I've got a few other bits and pieces in there, but the vast majority is the translation stuff, so you should see similar numbers in your schema.

ORA-29541: Error | Transluser

Rajeshwaran Jeyabal, September 13, 2016 - 6:13 am UTC

Ran this script from command line,

spool d:\query.lst
set termout off
select object_name, object_type, dbms_java.longname(object_name) lname
from   dba_objects
where  owner = 'TRANSLUSER'
and    object_type like 'JAVA%'
order by 2,1;
set termout on 
spool off


It returned 2355 rows.

.....
META-INF/MANIFEST.MF           JAVA RESOURCE           META-INF/MANIFEST.MF
META-INF/extension.xml         JAVA RESOURCE           META-INF/extension.xml
emulation/utils.pkb            JAVA RESOURCE           emulation/utils.pkb
emulation/utils.pks            JAVA RESOURCE           emulation/utils.pks
templates/generic.stg          JAVA RESOURCE           templates/generic.stg
templates/tsql.stg             JAVA RESOURCE           templates/tsql.stg

2355 rows selected.


Connor McDonald
September 14, 2016 - 1:02 am UTC

Sorry Rajeshwaran, I think you'll need to take this one with Support, because I cant reproduce the behaviour here :-(

Don't forget DBMS_ADVANCED_REWRITE

Mike, September 14, 2016 - 12:01 pm UTC

DBMS_ADVANCED_REWRITE (available since 10g) may be another solution to substitute one Oracle SQL statement for another.

To Mike | on DBMS_ADVANCED_REWRITE

Rajeshwaran, Jeyabal, September 14, 2016 - 2:29 pm UTC

DBMS_ADVANCED_REWRITE (available since 10g) may be another solution to substitute one Oracle SQL statement for another.

No, DBMS_ADVANCED_REWRITE - doesn't work in all cases. one such example is here
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:8730199800346896388
but that kind of SQL Substitution is possible using SQL Translation Framework in 12c (subjected that the SQL should present in Client application not in PL/SQL)

DBMS_ADVANCED_REWRITE Vs DBMS_SQL_TRANSLATOR

Rajeshwaran, Jeyabal, September 21, 2016 - 2:54 pm UTC

Tried to test the dbms_advanced_rewrite option from the above link in 12c.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

rajesh@ORA12C> declare
  2    l_src long;
  3    l_dest long;
  4  begin
  5    l_src :='select a.empno, a.ename, a.sal, sum(b.sal) '||
  6                               'from emp a, emp b '||
  7                               'WHERE a.sal > b.sal '||
  8                               'OR '||
  9                               '( a.sal= b.sal '||
 10                               'and a.rowid >= b.rowid ) '||
 11                               'group by a.empno, a.ename, a.rowid,a.sal '||
 12                               'order by a.sal, a.rowid  ' ;
 13    l_dest :='select empno,ename,sal, '||
 14    'sum(sal) over(order by sal,rowid) as new_sal '||
 15    'from emp ';
 16    sys.dbms_advanced_rewrite.declare_rewrite_equivalence
 17    (name                   =>'DEMO',
 18    source_stmt             =>l_src,
 19    destination_stmt  =>l_dest,
 20    validate                =>false,
 21    rewrite_mode      =>'TEXT_MATCH');
 22  end;
 23  /
declare
*
ERROR at line 1:
ORA-30389: the source statement is not compatible with the destination statement
ORA-00907: missing right parenthesis
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29
ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 185
ORA-06512: at line 16


So still the same bug exists in 12c, as a workaround went with SQL-Translator in 12c database.

rajesh@ORA12C> declare
  2     l_sql1 long;
  3     l_sql2 long;
  4  begin
  5     l_sql1 := 'select a.empno, a.ename, a.sal, sum(b.sal) from emp a,emp b WHERE a.sal > b.sal OR (a.sal= b.sal and a.rowid >= b.rowid) group by a.empno, a.ename, a.rowid,a.sal or
der by a.sal, a.rowid ';
  6     l_sql2 := 'select empno,ename,sal,sum(sal) over(order by sal,rowid) from emp';
  7     dbms_sql_translator.create_profile('MY_PROFILE');
  8     dbms_sql_translator.register_sql_translation('MY_PROFILE',l_sql1,l_sql2);
  9  end;
 10  /

PL/SQL procedure successfully completed.

rajesh@ORA12C> alter session set sql_translation_profile=MY_PROFILE;

Session altered.

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

Session altered.

rajesh@ORA12C> set serveroutput off
rajesh@ORA12C> select a.empno, a.ename, a.sal, sum(b.sal) from emp a,emp b WHERE a.sal > b.sal OR (a.sal= b.sal and a.rowid >= b.rowid) group by a.empno, a.ename, a.rowid,a.sal order
by a.sal, a.rowid ;

     EMPNO ENAME             SAL SUM(SAL)OVER(ORDERBYSAL,ROWID)
---------- ---------- ---------- ------------------------------
      7369 SMITH             800                            800
      7900 JAMES             950                           1750
      7876 ADAMS            1100                           2850
      7521 WARD             1250                           4100
      7654 MARTIN           1250                           5350
      7934 MILLER           1300                           6650
      7844 TURNER           1500                           8150
      7499 ALLEN            1600                           9750
      7782 CLARK            2450                          12200
      7698 BLAKE            2850                          15050
      7566 JONES            2975                          18025
      7788 SCOTT            3000                          21025
      7902 FORD             3000                          24025
      7839 KING             5000                          29025

14 rows selected.


Now looking into the plan confirms the above sql written using ANSI SQL Technique got replaced (transparently) with Analytic at run-time by the database.

rajesh@ORA12C> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID  gbadpz4u5q1t6, child number 0
-------------------------------------
select empno,ename,sal,sum(sal) over(order by sal,rowid) from emp

Plan hash value: 3145491563

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     4 (100)|          |
|   1 |  WINDOW SORT       |      |    14 |   364 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   364 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------


14 rows selected.

rajesh@ORA12C>


Thanks to SQL-Translator !
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here