Oracle DBMS_SQL_TRANSLATOR
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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_ERRORS VARCHAR2(30) 'LOG_ERRORS'
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
Security Model Owned by SYS with EXECUTE granted to PUBLIC

Another example we are calling out where a grant of EXECUTE to PUBLIC is unwarranted. A decision to translate or more correctly replace one SQL statement with another should be granted to an application ... not to everyone and anyone that can has the CREATE SESSION privilege.
Source {ORACLE_HOME}/rdbms/admin/dbmssqll.sql
Subprograms
CLEAR_SQL_TRANSLATION_ERROR EXPORT_PROFILE SET_SQL_TRANSLATION_COMMENT
CREATE_PROFILE IMPORT_PROFILE SET_SQL_TRANSLATION_MODULE
DEREGISTER_ERROR_TRANSLATION REGISTER_ERROR_TRANSLATION SQL_HASH
DEREGISTER_SQL_TRANSLATION REGISTER_SQL_TRANSLATION SQL_ID
DROP_PROFILE SET_ATTRIBUTE TRANSLATE_ERROR
ENABLE_ERROR_TRANSLATION SET_DICTIONARY_SQL_ID TRANSLATE_SQL
ENABLE_SQL_TRANSLATION SET_ERROR_TRANSLATION_COMMENT  
 
CLEAR_SQL_TRANSLATION_ERROR
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');

col txlrowner format a30
col txlrname format a30

SELECT * FROM sys.sqltxl$;

  OBJ# TXLROWNER  TXLRNAME   FLAGS AUDIT$
------ ---------- --------- ------ --------------
 98263 UWCLASS    TRANSPKG       3    

exec dbms_sql_translator.set_attribute('UW_TSQLTRANS', dbms_sql_translator.attr_trace_translation, dbms_sql_translator.attr_value_true);

SELECT * FROM sys.sqltxl$;

  OBJ# TXLROWNER  TXLRNAME   FLAGS AUDIT$
------ ---------- --------- ------ --------------
 98263 UWCLASS    TRANSPKG      19

-- attributes are stored in the FLAGS column
Flags Column Translation
flags number not null, /* flags */
                       /* 0x01 = foreign SQL dialect */
                       /* 0x02 = automatic translation registration */
                       /* 0x04 = custom translation miss alert */
                       /* 0x08 = custom translation miss error */
                       /* 0x10 = tracing */

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;

BEGIN
  execute immediate 'SELECT srvr_id INTO uwclass.tsql_target FROM uwclass.servers';
END;
/
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
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 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx