Oracle DBMS_SQL_TRANSLATOR
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
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