Oracle DBMS_SQL_TRANSLATOR
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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_TRANSLATOR VARCHAR2(30) 'TRANSLATOR'
ATTR_FOREIGN_SQL_SYNTAX VARCHAR2(30) 'FOREIGN_SQL_SYNTAX'
ATTR_TRANSLATION_REGISTRATION VARCHAR2(30) 'TRANSLATION_REGISTRATION'
ATTR_TRANSLATION_MISS_ALERT VARCHAR2(30) 'TRANSLATION_MISS_ALERT'
ATTR_TRANSLATION_MISS_ERROR VARCHAR2(30) 'TRANSLATION_MISS_ERROR'
ATTR_EDITIONABLE VARCHAR2(30) 'EDITIONABLE'
ATTR_TRACING VARCHAR2(30) 'TRACING'
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-029261 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
 
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_SQLTRANS', TRUE);

desc dba_sql_translation_profiles

col profile_name format a30
col translator format a11

SELECT *
FROM dba_sql_translation_profiles;

exec dbms_sql_translator.set_attribute('UW_SQLTRANS', dbms_sql_translator.attr_translator, 'migration_repo.sybase_tsql_translator');
exec dbms_sql_translator.set_attribute('UW_SQLTRANS', dbms_sql_translator.attr_editionable, dbms_sql_translator.attr_value_true);

BEGIN
  dbms_sql_translator.register_sql_translation(
    profile_name => 'UW_SQLTRANS',
    sql_text => 'select top 5 * from emp',
    translated_text => 'select * from emp where rownum <= 5');
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_SQLTRANS;

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 BLOB    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_ERROR_TRANSLATION_COMMENT (new 12.1)
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 (new 12.1)
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 (new 12.1)
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
DBMS_SQL_TRANSLATOR_EXPORT
Packages

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