Syntax

DBMS_SQL_TRANSLATOR.CREATE_PROFILE (
   profile_name    IN  VARCHAR2);

Parameters

Exceptions

Usage Notes

• A SQL translation profile is a database schema object that resides in SQL translation profile namespace. Its name follows the naming rules for database objects of the form [schema.]name. When the schema and profile names are used in the DBMS_SQL_TRANSLATOR package, they are uppercased unless surrounded by double quotation marks. For example, the translation profile profile_name => 'tsql_application' is the same as profile_name => 'Tsql_Application' and profile_name => 'TSQL_APPLICATION', but not the same as profile_name => '"tsql_application"'.

• A SQL translation profile is an editionable object type.

• A SQL translation profile cannot be created as a common object in a multitenant container database (CDB).

• To destroy a SQL translation profile, use the DROP_PROFILE Procedure.

Examples

BEGIN
  DBMS_SQL_TRANSLATOR.CREATE_PROFILE(profile_name => 'tsql_application');
END;

Syntax

DBMS_SQL_TRANSLATOR.DEREGISTER_SQL_TRANSLATION (
   profile_name      IN  VARCHAR2,
   sql_text          IN  CLOB);

Parameters

Exceptions

Examples

BEGIN
    DBMS_SQL_TRANSLATOR.DEREGISTER_SQL_TRANSLATION(
      profile_name => 'tsql_application',
      sql_text     => 'select top 5 * from emp');
END;

Syntax

DBMS_SQL_TRANSLATOR.DEREGISTER_ERROR_TRANSLATION (
   profile_name          IN   VARCHAR2,
   error_code            IN   PLS_INTEGER);

Parameters

Exceptions

Examples

BEGIN
   DBMS_SQL_TRANSLATOR.DEREGISTER_ERROR_TRANSLATION(
      profile_name    => 'tsql_application',
      error_code      => 1);
END;

Syntax

DBMS_SQL_TRANSLATOR.DROP_PROFILE (
   profile_name     IN    VARCHAR2);

Parameters

Exceptions

Examples

BEGIN
    DBMS_SQL_TRANSLATOR.DROP_PROFILE(
      profile_name    =>  'tsql_application');
END;

Syntax

DBMS_SQL_TRANSLATOR.ENABLE_ERROR_TRANSLATION (
   profile_name      IN  VARCHAR2,
   sql_text          IN CLOB,
   enable            IN BOOLEAN DEFAULT TRUE);

Parameters

Exceptions

Examples

BEGIN
  DBMS_SQL_TRANSLATOR.ENABLE_ERROR_TRANSLATION( 
      profile_name => 'tsql_application',
      sql_text     => 'SELECT TOP 5 * FROM emp'
      enable       => TRUE);
END;

Syntax

DBMS_SQL_TRANSLATOR.ENABLE_SQL_TRANSLATION (
   profile_name      IN    VARCHAR2,
   sql_text          IN    CLOB,
   enable            IN BOOLEAN DEFAULT TRUE);

Parameters

Exceptions

Examples

BEGIN
  DBMS_SQL_TRANSLATOR.ENABLE_SQL_TRANSLATION(
      profile_name => 'tsql_application',
      sql_text     => 'select top 5 * from emp',
      enable       => TRUE);
END;

Syntax

DBMS_SQL_TRANSLATOR.EXPORT_PROFILE (
   profile_name     IN          VARCHAR2,
   content          OUT NOCOPY  CLOB);

Parameters

Exceptions

Usage Notes

• The content of the SQL translation profile is exported in XML format as follows. Note that the profile name will not be exported.

  SQLTranslationProfile Translator="translator package name"
                         ForeignSQLSyntax="TRUE|FALSE"
                         TranslateNewSQL="TRUE|FALSE"
                         RaiseTranslationError="TRUE|FALSE"
                         LogTranslationError="TRUE|FALSE"
                         TraceTranslation="TRUE|FALSE"
                         Editionable="TRUE|FALSE">
    <SQLTranslations>
      <SQLTranslation Enabled="TRUE|FALSE">
        <SQLText>original SQL text</SQLText>
        <TranslatedText>translated SQL text</TranslatedText>
      </SQLTranslation>
      ...
    </SQLTranslations>
    <ErrorTranslations>
      <ErrorTranslation Enabled="TRUE|FALSE">
        <ErrorCode>Oracle error code</ErrorCode>
        <TranslatedCode>translated error code</TranslatedCode>
        <TranslatedSQLSTATE>translated SQLSTATE</TranslatedSQLSTATE>
      </ErrorTranslation>
      ...
    </ErrorTranslations>
  </SQLTranslationProfile>
  

• To import the content to a SQL translation profile, use the IMPORT_PROFILE Procedure.

Examples

DECLARE
  content CLOB;
BEGIN
    DBMS_SQL_TRANSLATOR.EXPORT_PROFILE(
      profile_name    =>  'tsql_application',
      content         =>  content);
END;

Syntax

DBMS_SQL_TRANSLATOR.IMPORT_PROFILE (
   profile_name       IN   VARCHAR2,
   content            IN   CLOB);

Parameters

Exceptions

Usage Notes

• The content of the SQL translation profile must be in XML format as used by the EXPORT_PROFILE Procedure. All elements and attributes are optional.

• If the profile does not exist, it is created. If it exists, the content overrides any existing attribute, translator package, SQL or error translation registration.

• To export the content to a SQL translation profile, use the EXPORT_PROFILE Procedure.

Examples

DECLARE
  content CLOB;
BEGIN
    DBMS_SQL_TRANSLATOR.IMPORT_PROFILE(
      profile_name    =>  'tsql_application',
      content         =>  content);
END;

Syntax

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);

Parameters

Exceptions

Usage Notes

• When the Oracle Database translates an Oracle error code using a translation profile, it searches for the registered custom translation first, and only invokes the translator package if no match is found.

• When a translation is registered in a profile, it may be disabled. Oracle Database does not search for disabled translations.

• The old translation of the error code and SQLSTATE, if present, is replaced with the new translation.

• To deregister a translation, use the DEREGISTER_ERROR_TRANSLATION Procedure.

Examples

BEGIN
      DBMS_SQL_TRANSLATOR.REGISTER_ERROR_TRANSLATION(
      profile_name    => 'tsql_application',
      error_code      => 1,
      translated_code => 2601);
END;

Syntax

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);

Parameters

Exceptions

Usage Notes

• When the Oracle Database translates a statement using a translation profile, it searches for the registered custom translation first, and only invokes the translator package if no match is found.

• When a translation is registered in a profile, it may be disabled. Oracle Database does not search for disabled translations.

• When translated_text is NULL, no translation is required and the original statement is used.

• The old translation of the SQL statement, if present, is replaced with the new translation.

• To deregister a translation, use the DEREGISTER_SQL_TRANSLATION Procedure.

Examples

BEGIN
    DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION(
      profile_name    => 'tsql_application',
      sql_text        => 'select top 5 * from emp',
      translated_text => 'SELECT * FROM emp WHERE rownum <= :SYS_N_001');
END;

Syntax

DBMS_SQL_TRANSLATOR.SET_ATTRIBUTE (
   profile_name      IN  VARCHAR2,
   attribute_name    IN  VARCHAR2,
   attribute_value   IN  VARCHAR2;)

Parameters

Exceptions

Usage Notes

See Constants

Syntax

DBMS_SQL_TRANSLATOR.SQL_HASH (
   sql_text         IN    CLOB) 
RETURN NUMBER DETERMINISTIC;

Parameters

Return Values

Returns hash value of the SQL statement in the SQL translation profile

Exceptions

Examples

DECLARE
  sqltext CLOB;
  txltext CLOB;
  sqlhash NUMBER;
BEGIN
  sqltext  :=  'SELECT TOP 1 * FROM emp';
  sqlhash  :=  DBMS_SQL_TRANSLATOR.SQL_HASH (sqltext);
  SELECT translated_text INTO txltext
    FROM user_sql_translations
    WHERE sql_hash = sqlhash 
    AND DBMS_LOB.COMPARE (sql_text, sqltext) = 0;
END;

Syntax

DBMS_SQL_TRANSLATOR.SQL_ID (
   sql_text         IN    CLOB) 
RETURN VARCHAR2 DETERMINISTIC;

Parameters

Return Values

Returns the SQL ID of the SQL statement in the SQL translation profile

Exceptions

Examples

DECLARE
  sqltext CLOB;
  sqlid   VARCHAR2(13);
BEGIN
  sqltext :=  'SELECT TOP 1 * FROM emp';
  sqlid   :=  DBMS_SQL_TRANSLATOR.SQL_ID (sqltext);
END;

Syntax

DBMS_SQL_TRANSLATOR.TRANSLATE_ERROR (
   error_code           IN           PLS_INTEGER,
   translated_code      OUT          PLS_INTEGER,
   translated_sqlstate  OUT NOCOPY   VARCHAR2);

Parameters

Exceptions

Examples

DECLARE
  translated_code      BINARY_INTEGER;
  translated_sqlstate  VARCHAR2(5);
BEGIN
    DBMS_SQL_TRANSLATOR.TRANSLATE_ERROR(
      error_code           => 1,
      translated_code      => translated_code,
      translated_sqlstate  => translated_sqlstate);
END;

Syntax

DBMS_SQL_TRANSLATOR.TRANSLATE_SQL (
   sql_text          IN           CLOB,
   translated_text   OUT  NOCOPY  CLOB);

Parameters

Exceptions

Examples

ALTER SESSION SET SQL_TRANSLATION_PROFILE =  tsql_application;
 
DECLARE
  translated_text CLOB;
BEGIN
    DBMS_SQL_TRANSLATOR.TRANSLATE_SQL(
      sql_text         => 'select top 5 * from emp',
      translated_text  => translated_text);
END;