Oracle DBMS_SQL_TRANSLATOR
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose A SQL translation profile is an editionable database schema object that resides in SQL translation profile namespace. A SQL translation profile cannot be created as a common object in a consolidated database.
AUTHID CURRENT_USER
Constants
Name Data Type Value
ATTR_EDITIONABLE VARCHAR2(30) 'EDITIONABLE'
ATTR_FOREIGN_SQL_SYNTAX VARCHAR2(30) 'FOREIGN_SQL_SYNTAX'
ATTR_LOG_TRANSLATION_ERROR VARCHAR2(30) 'LOG_TRANSLATION_ERROR'
ATTR_RAISE_TRANSLATION_ERROR VARCHAR2(30) 'RAISE_TRANSLATION_ERROR'
ATTR_TRACE_TRANSLATION VARCHAR2(30) 'TRACE_TRANSLATOR'
ATTR_TRANSLATE_NEW_SQL VARCHAR2(30) 'TRANSLATE_NEW_SQL'
ATTR_TRANSLATOR VARCHAR2(30) 'TRANSLATOR'
ATTR_VALUE_TRUE VARCHAR2(30) 'TRUE'
ATTR_VALUE_FALSE VARCHAR2(30) 'FALSE'
Dependencies
ALL_ERROR_TRANSLATIONS DBMS_SQL_TRANSLATOR_LIB SYS_IXMLAGG
ALL_OBJECTS DBMS_STANDARD XMLAGG
ALL_SQL_TRANSLATIONS DUAL XMLTYPE
ALL_SQL_TRANSLATION_PROFILES SQLTXL$ XQSEQUENCE
DBMS_SQL_TRANSLATOR_EXPORT    
Documented Yes
Exceptions
Error Code Reason
ORA-00955 profile_exists
ORA-01031 insufficient_privilege
ORA-01435 no_such_user
ORA-24252 no_such_profile
ORA-24253 no_translation_found
ORA-29261 bad_argument
First Available 12.1.0
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmssqll.sql
Subprograms
 
CLEAR_SQL_TRANSLATION_ERROR (new 12.2)
Clears the last error when the SQL was run dbms_sql_translator.clear_sql_translation_error(
profile_name IN VARCHAR2,
sql_text     IN CLOB);
PRAGMA SUPPLEMENTAL_LOG_DATA(clear_sql_translation_error, AUTO_WITH_COMMIT);
TBD
 
CREATE_PROFILE
Create a translation profile dbms_sql_translator.create_profile(
profile_name IN VARCHAR2,
editionable  IN BOOLEAN DEFAULT TRUE);
PRAGMA SUPPLEMENTAL_LOG_DATA(create_profile, AUTO_WITH_COMMIT);
conn sys@pdbdev as sysdba

exec dbms_sql_translator.create_profile('UW_TSQLTRANS', TRUE);

desc dba_sql_translation_profiles

col profile_name format a20
col translator format a11

SELECT *
FROM dba_sql_translation_profiles;

CREATE OR REPLACE PACKAGE uwclass.transpkg AUTHID CURRENT_USER IS
 PROCEDURE translate_sql(sql_text        IN  CLOB,
                         translated_text OUT NOCOPY CLOB);
 PROCEDURE translate_error(error_code          IN  BINARY_INTEGER,
                           translated_code     OUT BINARY_INTEGER,
                           translated_sqlstate OUT NOCOPY VARCHAR2);
END transpkg;
/

exec dbms_sql_translator.set_attribute('UW_TSQLTRANS', dbms_sql_translator.attr_translator, 'uwclass.transpkg');
exec dbms_sql_translator.set_attribute('UW_TSQLTRANS', dbms_sql_translator.attr_editionable, dbms_sql_translator.attr_value_true);

-- do not know where these attributes are stored but continuing to watch for it.

CREATE TABLE uwclass.tsql_target AS
SELECT srvr_id
FROM uwclass.servers
WHERE 1=2;

exec dbms_sql_translator.register_sql_translation(
  profile_name => 'UW_TSQLTRANS',
  sql_text => 'SELECT srvr_id INTO uwclass.tsql_target FROM uwclass.servers',
  translated_text => 'INSERT INTO uwclass.tsql_target SELECT srvr_id FROM uwclass.servers');

-- SELECT getdate(); vs SELECT sysdate FROM dual;

desc dba_sql_translations

col owner format a10
col sql_text format a40
col translated_text format a39

SELECT owner, profile_name, sql_text, translated_text
from dba_sql_translations;

SQL> BEGIN
2 execute immediate 'SELECT srvr_id INTO uwclass.tsql_target FROM uwclass.servers';
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-00905: missing keyword
ORA-06512: at line 2


ALTER SESSION SET SQL_TRANSLATION_PROFILE = UW_TSQLTRANS;
ALTER SESSION SET EVENTS = '10601 trace name context forever, level 32';

BEGIN
  execute immediate 'SELECT srvr_id INTO uwclass.tsql_target FROM uwclass.servers';
END;
/


DECLARE
 hashVal NUMBER;
 retVal  VARCHAR2(30);
 sqlTxt  CLOB := 'select top 5 * from emp';
BEGIN
  hashVal := dbms_sql_translator.sql_hash(sqlTxt);
  dbms_output.put_line(TO_CHAR(hashVal));

  retVal := dbms_sql_translator.sql_id(sqlTxt);
  dbms_output.put_line(TO_CHAR(retVal));
END;
/

ALTER SESSION SET SQL_TRANSLATION_PROFILE = UW_TSQLTRANS;

DECLARE
 iClob CLOB := 'select top 5 * from emp';
 oCLOB CLOB;
BEGIN
  dbms_sql_translator.translate_sql(iClob, oClob);
  dbms_output.put_line(oClob);
END;
/

exec dbms_sql_translator.drop_profile('UW_SQLTRANS');

SELECT *
FROM dba_sql_translation_profiles;
 
DEREGISTER_ERROR_TRANSLATION
Deregisters the translation of an Oracle error code and SQLSTATE in a SQL translation profile dbms_sql_translator.procedure register_error_translation(
profile_name        in VARCHAR2,
error_code          IN PLS_INTEGER,
translated_code     IN PLS_INTEGER DEFAULT NULL,
translated_sqlstate in VARCHAR2    DEFAULT NULL,
enable              IN BOOLEAN     DEFAULT TRUE);
PRAGMA SUPPLEMENTAL_LOG_DATA(register_error_translation, AUTO_WITH_COMMIT);
BEGIN
  dbms_sql_translator.deregister_error_translation(profile_name => UW_SQLTRANS', error_code => 1);
END;
/
 
DEREGISTER_SQL_TRANSLATION
Deregisters the custom translation of a SQL statement in a SQL translation profile dbms_sql_translator.procedure deregister_sql_translation(
profile_name IN VARCHAR2,
sql_text     IN CLOB);
PRAGMA SUPPLEMENTAL_LOG_DATA(deregister_sql_translation, AUTO_WITH_COMMIT);
BEGIN
  dbms_sql_translator.deregister_sql_translation('UW_SQLTRANS', 'select top 5 * from emp');
END;
/
 
DROP_PROFILE
Drop a translation profile dbms_sql_translator.drop_profile(profile_name IN VARCHAR2);
PRAGMA SUPPLEMENTAL_LOG_DATA(drop_profile, AUTO_WITH_COMMIT);
See CREATE_PROFILE Demo Above
 
ENABLE_ERROR_TRANSLATION
Enables a custom translation of an Oracle error code in a SQL translation profile dbms_sql_translator.enable_error_translation(
profile_name  IN VARCHAR2,
error_code    IN PLS_INTEGER,
enable        IN BOOLEAN DEFAULT TRUE);
PRAGMA SUPPLEMENTAL_LOG_DATA(enable_error_translation, AUTO_WITH_COMMIT);
BEGIN
  dbms_sql_translator.enable_error_translation('UW_SQLTRANS', 1, TRUE);
END;
/
 
ENABLE_SQL_TRANSLATION
Enables a custom translation of an Oracle error code in a SQL translation profile dbms_sql_translator.enable_sql_translation(
profile_name IN VARCHAR2,
sql_text     IN CLOB,
enable       IN BOOLEAN DEFAULT TRUE);
PRAGMA SUPPLEMENTAL_LOG_DATA(enable_sql_translation, AUTO_WITH_COMMIT);
BEGIN
  dbms_sql_translator.enable_sql_translation('UW_SQLTRANS', 'select top 5 * from emp', TRUE);
END;
/
 
EXPORT_PROFILE
Exports the content of a SQL translation profile dbms_sql_translator.export_profile(profile_name IN VARCHAR2, content OUT NOCOPY CLOB);
DECLARE
 lRetVal CLOB;
BEGIN
  dbms_sql_translator.export_profile('UW_SQLTRANS',  lRetVal);
END;
/
 
 
IMPORT_PROFILE
Imports the content of a SQL translation profile dbms_sql_translator.import_profile(profile_name IN VARCHAR2, content IN CLOB);
DECLARE
 lContent CLOB;
BEGIN
  dbms_sql_translator.import_profile(profile_name => 'UW_SQLTRANS', content => content);
END;
/
 
REGISTER_ERROR_TRANSLATION
Registers a custom translation of an Oracle error code and SQLSTATE in a SQL translation profile dbms_sql_translator.register_error_translation(
profile_name        IN VARCHAR2,
error_code          IN PLS_INTEGER,
translated_code     IN PLS_INTEGER DEFAULT NULL,
translated_sqlstate IN VARCHAR2    DEFAULT NULL,
enable              IN BOOLEAN     DEFAULT TRUE);
PRAGMA SUPPLEMENTAL_LOG_DATA(register_error_translation, AUTO_WITH_COMMIT);
BEGIN
  dbms_sql_translator.register_error_translation(profile_name => 'UW_SQLTRANS', error_code => 1, translated_code => 2601);
END;
/
 
REGISTER_SQL_TRANSLATION
Registers a custom translation of a SQL statement in a SQL translation profile dbms_sql_translator.register_sql_translation(
profile_name    IN VARCHAR2,
sql_text        IN CLOB,
translated_text IN CLOB    DEFAULT NULL,
enable          IN BOOLEAN DEFAULT TRUE);
PRAGMA SUPPLEMENTAL_LOG_DATA(register_sql_translation, AUTO_WITH_COMMIT);
See CREATE_PROFILE Demo Above
 
SET_ATTRIBUTE
Sets an attribute of a SQL translation profile dbms_sql_translator.set_attribute(
profile_name    IN VARCHAR2,
attribute_name  IN VARCHAR2,
attribute_value IN VARCHAR2);
PRAGMA SUPPLEMENTAL_LOG_DATA(set_attribute, AUTO_WITH_COMMIT);
See CREATE_PROFILE Demo Above
 
SET_DICTIONARY_SQL_ID (new 12.2)
Sets the SQL identifier of the SQL text in translation dictionary used to translate the current SQL statement dbms_sql_translator.set_dictionary_sql_id(dictionary_sql_id IN VARCHAR2);
exec dbms_sql_translator.set_dictionary_sql_id('b4yz288n4gyc6');
 
SET_ERROR_TRANSLATION_COMMENT
Sets the comment on a custom translation of an Oracle error code in a SQL translation profile set_error_translation_comment(
profile_name IN VARCHAR2,
error_code   IN PLS_INTEGER,
comment      IN VARCHAR2);
BEGIN
  dbms_sql_translator.set_error_translation_comment('UW_SQLTRANS', 1, 'Well there you go again');
END;
/
 
SET_SQL_TRANSLATION_COMMENT
Sets the comment on a custom translation of a SQL statement in a SQL translation profile dbms_sql_translator.set_sql_translation_comment(
profile_name IN VARCHAR2,
sql_text     IN CLOB,
comment      IN VARCHAR2);
BEGIN
  dbms_sql_translator.set_sql_translation_comment('UW_SQLTRANS', 'select top 5 * from emp', 'High 5');
END;
/
 
SET_SQL_TRANSLATION_MODULE
Sets the module and action on a custom translation of a SQL statement in a SQL translation profile dbms_sql_translator.set_sql_translation_module(
profile_name in VARCHAR2,
sql_text IN CLOB,
module   IN VARCHAR2,
action   IN VARCHAR2)
BEGIN
  dbms_sql_translator.set_sql_translation_comment('UW_SQLTRANS', 'select top 5 * from emp', 'SQL Translation Demo', 'Translation Test);
END;
/

SELECT module, action
FROM v$session
WHERE module = 'SQL Translation Demo';
 
SQL_HASH
Computes the hash value of a SQL statement in a SQL translation profile dbms_sql_translator.sql_hash(sql_text IN CLOB)
RETURN NUMBER DETERMINISTIC;
See CREATE_PROFILE Demo Above
 
SQL_ID
Computes the SQL identifier of a SQL statement in a SQL translation profile dbms_sql_translator.sql_id(sql_text IN CLOB)
RETURN VARCHAR2 DETERMINISTIC;
See CREATE_PROFILE Demo Above
 
TRANSLATE_ERROR
Translates an Oracle error code and an ANSI SQLSTATE using a SQL translation profile dbms_sql_translator.translate_error(
error_code          IN         PLS_INTEGER,
translated_code     OUT        PLS_INTEGER,
translated_sqlstate OUT NOCOPY VARCHAR2);
ALTER SESSION SET SQL_TRANSLATION_PROFILE = UW_SQLTRANS;

DECLARE
 x PLS_INTEGER;
 y VARCHAR2(60);
BEGIN
  dbms_sql_translator.translate_error(1, x, y);
  dbms_output.put_line(x);
  dbms_output.put_line(y);
END;
/
 
TRANSLATE_SQL
Translates a SQL statement using a SQL translation profile dbms_sql_translator.translate_sql(
sql_text        IN  CLOB,
translated_text OUT NOCOPY CLOB);
See CREATE_PROFILE Demo Above

Related Topics
Built-in Functions
Built-in Packages
DBMS_ADVANCED_REWRITE
DBMS_SQL_TRANSLATOR_EXPORT
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved