SQL Translation Framework: A Step-by-Step Guide to Intercepting and Rewriting Queries

Modern enterprise applications often contain SQL statements that are difficult or even impossible to change directly. In many environments, the source code is maintained by third-party vendors, legacy development teams, or packaged applications where modifying SQL is risky, time-consuming, or unsupported. Yet DBAs and architects are frequently confronted with situations where SQL behavior must be adjusted — whether for performance tuning, compatibility, testing, security controls, or operational workarounds.

This is where the Oracle Database SQL Translation Framework becomes an extremely powerful and often overlooked feature.

Originally introduced to improve compatibility with non-Oracle SQL dialects, the SQL Translation Framework can also intercept and transparently rewrite SQL statements at runtime before they are executed by the database. This enables database administrators to modify application SQL behavior without touching the application code itself.

In this article, we will demonstrate a simple but illustrative example: transparently transforming the query

select max(object_id) from all_objects;

into

select min(object_id) from all_objects;

using the SQL Translation Framework.

Although this example is intentionally simple, it highlights the core capabilities of the feature:

• runtime SQL interception
• SQL rewriting without code changes
• centralized control inside the database
• transparent behavior for applications

Beyond demonstrations, the same mechanism can be used in real-world scenarios such as:

• replacing deprecated syntax
• implementing temporary SQL workarounds
• redirecting objects or schemas
• enforcing standards
• assisting migrations between database platforms
• mitigating problematic SQL generated by applications

By the end of this post, you will understand how the SQL Translation Framework works, how translation profiles are configured, and how SQL statements can be dynamically transformed during execution in Oracle Database.

First we will run the two SQL statements without having any Translations in place:

SQL> select min(object_id) from all_objects;

   MIN(OBJECT_ID)
_________________
                2

1 row selected.

SQL> select max(object_id) from all_objects;

   MAX(OBJECT_ID)
_________________
            94030

1 row selected.

SQL>

Next we will create a SQL Translation Profile, register the Translation and activate it for the current session:

-- create profile STP1
exec dbms_sql_translator.create_profile('STP1');
-- register the translation
exec dbms_sql_translator.register_sql_translation('STP1','select max(object_id) from all_objects','select min(object_id) from all_objects');
-- enable the translation to work with Oracle db clients (like SQL*Plus SQLcl)
exec dbms_sql_translator.set_attribute('STP1', dbms_sql_translator.attr_foreign_sql_syntax, dbms_sql_translator.attr_value_false);
grant all on sql translation profile stp1 to user1;
-- activate the profile for the current session
alter session set sql_translation_profile = STP1;

SQL> -- create profile STP1
SQL> exec dbms_sql_translator.create_profile('STP1');

PL/SQL procedure successfully completed.

SQL> -- register the translation
SQL> exec dbms_sql_translator.register_sql_translation('STP1','select max(object_id) from all_objects','select min(object_id) from all_objects');

PL/SQL procedure successfully completed.

SQL> -- enable the translation to work with Oracle db clients (like SQL*Plus SQLcl)
SQL> exec dbms_sql_translator.set_attribute('STP1', dbms_sql_translator.attr_foreign_sql_syntax, dbms_sql_translator.attr_value_false);

PL/SQL procedure successfully completed.

SQL> grant all on sql translation profile stp1 to user1;

Grant succeeded.

SQL> -- activate the profile for the current session
SQL> alter session set sql_translation_profile = STP1;

Session altered.

SQL>

If we now re run the queries from above we can see, that the second statement has been replaced by another SQL statement:

SQL> select min(object_id) from all_objects;

   MIN(OBJECT_ID)
_________________
                2

1 row selected.

SQL> select max(object_id) from all_objects;

   MIN(OBJECT_ID)
_________________
                2

1 row selected.

SQL>

The SQL Translation Framework in Oracle Database demonstrates how flexible and extensible the database engine can be when dealing with application SQL that cannot easily be modified at the source. By transparently intercepting and rewriting SQL statements at runtime, DBAs and architects gain a powerful mechanism to influence application behavior without requiring code changes, recompilation, or vendor involvement.

For Oracle DBAs, performance engineers, and architects, the SQL Translation Framework is one of those advanced features that can solve highly specific problems elegantly when traditional approaches are not feasible. While it may not be required in everyday database administration, understanding its capabilities adds another valuable tool to the Oracle troubleshooting and optimization toolbox.

Useful statements

-- disable the translation profile for the current session
alter session set sql_translation_profile=null;
-- get infos about translation profiles and translations
select * from dba_sql_translation_profiles;
select * from dba_sql_translations;
-- deregister a translation
exec dbms_sql_translator.deregister_sql_translation ('stp1', 'select max(object_id) from all_objects')
-- drop a translation profile
exec dbms_sql_translator.drop_profile('STP1');

Creating a logon trigger to activate the Translation Profile when the application connects to the database

create or replace trigger user1.tr_stp
after logon on user1.schema
begin
  execute immediate 'alter session set sql_translation_profile=STP1';
exception
  when others then
    null;
end;
/

-- disable, enable and drop the trigger
alter trigger user1.tr_stp disable;
alter trigger user1.tr_stp enable;
drop trigger user1.tr_stp;

It is important that the SQL text for the translations match exactly. Based on this you can use the following PL/SQL block to register a translation based on SQL IDs. Make sure the source and the target statements have run recently and are available in the shared pool:

DECLARE
   -- Define the two SQL IDs (replace with actual values or accept as parameters)
   v_sql_id_source   VARCHAR2(13) := '&source_sqlid';
   v_sql_id_target   VARCHAR2(13) := '&target_sqlid';
   v_profile_name    VARCHAR2(30) := 'STP1';
   v_source_sql      CLOB;
   v_target_sql      CLOB;
BEGIN
   -- Fetch SQL_FULLTEXT for the source SQL_ID
   SELECT sql_fulltext
     INTO v_source_sql
     FROM v$sql
    WHERE sql_id = v_sql_id_source
      AND ROWNUM = 1;   -- In case of multiple child cursors, pick one

   -- Fetch SQL_FULLTEXT for the target SQL_ID
   SELECT sql_fulltext
     INTO v_target_sql
     FROM v$sql
    WHERE sql_id = v_sql_id_target
      AND ROWNUM = 1;

   -- Register the translation
   dbms_sql_translator.register_sql_translation(
      profile_name    => v_profile_name,
      sql_text        => v_source_sql,
      translated_text => v_target_sql
   );

   dbms_output.put_line('Translation registered successfully.');
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      dbms_output.put_line('Error: One or both SQL IDs not found in V$SQL.');
   WHEN OTHERS THEN
      dbms_output.put_line('Error: ' || SQLERRM);
END;
/

To deregister a translation based on the source SQL ID you can use the following snippet. If the SQL ID is not known you can query dba_sql_translations that contains the source SQL ID:

DECLARE
   -- Name of the translation profile that contains the mapping
   v_profile_name   VARCHAR2(30) := 'STP1';

   -- Enter the SOURCE SQL_ID (the SQL that is being translated)
   -- This is the SQL_ID of the original statement that you want to stop translating.
   v_source_sql_id  VARCHAR2(13) := '&source_sqlid';

   -- Will hold the exact source SQL text fetched from the data dictionary
   v_source_sql     CLOB;
BEGIN
   -- Retrieve the exact source SQL text using the SOURCE SQL_ID
   SELECT sql_text
   INTO   v_source_sql
   FROM   dba_sql_translations
   WHERE  sql_id = v_source_sql_id
     AND  ROWNUM = 1;          -- safety in case of duplicates (should not happen)

   -- Deregister the translation using the source SQL text
   DBMS_SQL_TRANSLATOR.DEREGISTER_SQL_TRANSLATION(
      profile_name => v_profile_name,
      sql_text     => v_source_sql
   );

   DBMS_OUTPUT.PUT_LINE('Deregistered translation for source SQL_ID: ' || v_source_sql_id);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('No translation found for source SQL_ID ' || v_source_sql_id ||
                           ' in profile ' || v_profile_name);
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/