158 DBMS_SQL_TRANSLATOR
The DBMS_SQL_TRANSLATOR
package provides an interface for creating, configuring, and using SQL translation profiles.
This chapter contains the following topics:
See Also:
SQL Translation Framework Architecture and Overview inOracle Database Migration Guide
158.1 DBMS_SQL_TRANSLATOR Security Model
DBMS_SQL_TRANSLATOR
is a invoker's rights package.
When translating a SQL statement or error, the translator package procedure will be invoked with the same current user and current schema as those in which the SQL statement being parsed. The owner of the translator package must be granted the TRANSLATE SQL
user privilege on the current user.
Additionally, the current user must be granted the EXECUTE
privilege on the translator package.
158.2 DBMS_SQL_TRANSLATOR Constants
DBMS_SQL_TRANSLATOR
defines several constants to use when specifying parameter values.
These are shown in the following table.
Table 158-1 DBMS_SQL_TRANSLATOR Constants
Constant | Value | Type | Description |
---|---|---|---|
|
|
|
Name of the SQL translation profile attribute that specifies whether the SQL translation profile becomes an editioned or noneditioned object if editioning is later enabled for the schema object type SQL translation profile in the owner's schema (see Operational Notes) |
|
|
|
Name of the SQL translation profile attribute that indicates if the profile is for translation of foreign SQL syntax (see Operational Notes) |
|
|
|
Name of the SQL translation profile attribute that controls if the profile should log translation error in the database alert log (see Operational Notes) |
|
|
|
Name of the SQL translation profile attribute that controls if the profile should raise translation error if a SQL statement or error fails to be translated (see Operational Notes) |
|
|
|
Name of the SQL translation profile attribute that controls if the profile should translate new SQL statements and errors (see Operational Notes) |
|
|
|
Name of the SQL translation profile attribute that controls tracing (see Operational Notes) |
|
|
|
Name of the SQL translation profile attribute that specifies the translator package (see Operational Notes) |
|
|
|
Value to set a SQL translation profile attribute to true (see Operational Notes) |
|
|
|
Value to set a SQL translation profile attribute to false (see Operational Notes) |
158.3 DBMS_SQL_TRANSLATOR Operational Notes
The subprograms that modify a profile have DDL transaction semantics and when invoked will commit any open transaction in the session.
ATTR_EDITIONABLE Constant
Editionable is true by default.
ATTR_FOREIGN_SQL_SYNTAX Constant
Foreign SQL syntax is true by default.
ATTR_LOG_TRANSLATION_ERROR Constant
-
If log translation is enabled in a SQL translation profile, an alert log is written to the database alert log if no custom translation is found for a SQL statement or error. This allows the user to catch any error in the custom translation in a profile.
-
Log translation error is false by default.
ATTR_RAISE_TRANSLATION_ERROR Constant
Raise translation error is false by default.
ATTR_TRANSLATE_NEW_SQL Constant
-
The name of the SQL translation profile attribute that controls if the profile should translate new SQL statements and errors. If so, the translator package, if registered, will translate a new SQL statement or error not already translated in custom translations, and also register the new translation as custom translation. If not, any new SQL statement or error encountered will result in a translation error
-
Translate new SQL statements and errors is true by default.
ATTR_TRACE_TRANSLATION Constant
-
If tracing is enabled in a SQL translation profile, any SQL statement or error translated by the profile in a database session and its translation is written to the database session's trace file.
-
Tracing is disabled by default.
ATTR_TRANSLATOR Constant
-
The translator package must be a PL/SQL package with the following three procedures. The TRANSLATE_SQL Procedure and the TRANSLATE_ERROR Procedure are called to translate SQL statements and errors. The names of the parameters of the translate procedures must be followed.
PROCEDURE TRANSLATE_SQL( sql_text IN CLOB, translated_text OUT CLOB); PROCEDURE TRANSLATE_ERROR( error_code IN BINARY_INTEGER, translated_code OUT BINARY_INTEGER, translated_sqlstate OUT VARCHAR2); Parameters: profile_name - profile name sql_text - SQL statement to be translated translated_text - translated SQL statement error_code - Oracle error code translated_code - translated error code translated_sqlstate - translated SQLSTATE
-
When
NULL
is returned intranslated_text
,translated_code
, ortranslated_sqlstate
, it means that no translation is required and the original SQL statement, error code, orSQLSTATE
is used instead. -
The name of the translator package follows the naming rules for database packages of the form
[schema.]package_name
. When the schema and package names are used, they are set to uppercase by default unless surrounded by double quotation marks. For example, setting a translator package,translator => 'dbms_tsql_translator'
is the same astranslator => 'Dbms_Tsql_Translator'
and translator => 'DBMS_TSQL_TRANSLATOR'
, but not the same astranslator => '"dbms_tsql_translator"'
. The default schema name is the profile owner. -
The translator attribute is not set by default.
ATTR_VALUE_TRUE Constant
The value to set a SQL translation profile attribute to true.
ATTR_VALUE_FALSE Constant
The value to set a SQL translation profile attribute to false.
158.4 DBMS_SQL_TRANSLATOR Exceptions
This table lists the exceptions raised by the DBMS_SQL_TRANSLATOR
package.
Table 158-2 Exceptions Raised by DBMS_SQL_TRANSLATOR
Exception | Error Code | Description |
---|---|---|
|
|
Bad argument is passed to the PL/SQL interface |
|
|
User has insufficient privilege for the operation |
|
|
Profile does not exist |
|
|
Profile owner does not exist |
|
|
No translation of the SQL statement or error code found |
|
|
Profile already exists |
158.5 DBMS_SQL_TRANSLATOR Examples
This is an example of basic SQL translation using DBMS_SQL_TRANSLATOR
.
Basic SQL Translation
BEGIN DBMS_SQL_TRANSLATOR.CREATE_PROFILE( profile_name => ' tsql_application'); DBMS_SQL_TRANSLATOR.SET_ATTRIBUTE( profile_name => 'tsql_application', attribute_name => DBMS_SQL_TRANSLATOR.ATTR_TRANSLATOR, attribute_value => 'migration_repo.sybase_tsql_translator'); END;
158.6 Summary of DBMS_SQL_TRANSLATOR Subprograms
This table lists the DBMS_SQL_TRANSLATOR
subprograms and briefly describes them.
Table 158-3 DBMS_SQL_TRANSLATOR Package Subprograms
Subprogram | Description |
---|---|
Creates a SQL translation profile |
|
Deregisters the custom translation of a SQL statement in a SQL translation profile |
|
Deregisters the translation of an Oracle error code and |
|
Drops a SQL translation profile and its contents |
|
Enables or disables a custom translation of an Oracle error code in a SQL translation profile |
|
Enables or disables a custom translation of a SQL statement in a SQL translation profile |
|
Exports the content of a SQL translation profile |
|
Imports the content of a SQL translation profile |
|
Registers a custom translation of an Oracle error code and |
|
Registers a custom translation of a SQL statement in a SQL translation profile |
|
Sets an attribute of a SQL translation profile |
|
Computes the hash value of a SQL statement in a SQL translation profile |
|
Computes the SQL identifier of a SQL statement in a SQL translation profile |
|
Translates an Oracle error code and an ANSI |
|
Translates a SQL statement using a SQL translation profile |
158.6.1 CREATE_PROFILE Procedure
This procedure creates a SQL translation profile.
Syntax
DBMS_SQL_TRANSLATOR.CREATE_PROFILE ( profile_name IN VARCHAR2);
Parameters
Table 158-4 CREATE_PROFILE Procedure Parameters
Parameter | Description |
---|---|
|
Name of profile |
Exceptions
Table 158-5 CREATE_PROFILE Procedure Exceptions
Exception | Description |
---|---|
|
Bad argument is passed to the PL/SQL interface |
|
User has insufficient privilege for the operation |
|
Profile owner does not exist |
|
Profile already exists |
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 theDBMS_SQL_TRANSLATOR
package, they are uppercased unless surrounded by double quotation marks. For example, the translation profileprofile_name => 'tsql_application'
is the same asprofile_name => 'Tsql_Application'
andprofile_name => 'TSQL_APPLICATION'
, but not the same asprofile_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;
158.6.2 DEREGISTER_SQL_TRANSLATION Procedure
This procedure deregisters the custom translation of a SQL statement in a SQL translation profile.
Syntax
DBMS_SQL_TRANSLATOR.DEREGISTER_SQL_TRANSLATION ( profile_name IN VARCHAR2, sql_text IN CLOB);
Parameters
Table 158-6 DEREGISTER_SQL_TRANSLATION Procedure Parameters
Parameter | Description |
---|---|
|
Name of profile |
|
SQL statement |
Exceptions
Table 158-7 DEREGISTER_SQL_TRANSLATION Procedure Exceptions
Exception | Description |
---|---|
|
Bad argument is passed to the PL/SQL interface |
|
User has insufficient privilege for the operation |
|
Profile owner does not exist |
|
Profile already exists |
Examples
BEGIN DBMS_SQL_TRANSLATOR.DEREGISTER_SQL_TRANSLATION( profile_name => 'tsql_application', sql_text => 'select top 5 * from emp'); END;
158.6.3 DEREGISTER_ERROR_TRANSLATION Procedure
This procedure deregisters the translation of an Oracle error code and SQLSTATE
in a SQL translation profile.
Syntax
DBMS_SQL_TRANSLATOR.DEREGISTER_ERROR_TRANSLATION ( profile_name IN VARCHAR2, error_code IN PLS_INTEGER);
Parameters
Table 158-8 DEREGISTER_ERROR_TRANSLATION Procedure Parameters
Parameter | Description |
---|---|
|
Name of profile |
|
Oracle error code |
Exceptions
Table 158-9 DEREGISTER_ERROR_TRANSLATION Procedure Exceptions
Exception | Description |
---|---|
|
Bad argument is passed to the PL/SQL interface |
|
User has insufficient privilege for the operation |
|
Profile owner does not exist |
|
Profile does not exist |
Examples
BEGIN DBMS_SQL_TRANSLATOR.DEREGISTER_ERROR_TRANSLATION( profile_name => 'tsql_application', error_code => 1); END;
158.6.4 DROP_PROFILE Procedure
This procedure drops a SQL translation profile and its contents.
Syntax
DBMS_SQL_TRANSLATOR.DROP_PROFILE ( profile_name IN VARCHAR2);
Parameters
Table 158-10 DROP_PROFILE Procedure Parameters
Parameter | Description |
---|---|
|
Name of profile |
Exceptions
Table 158-11 DROP_PROFILE Procedure Exceptions
Exception | Description |
---|---|
|
Bad argument is passed to the PL/SQL interface |
|
User has insufficient privilege for the operation |
|
Profile owner does not exist |
|
Profile does not exist |
Examples
BEGIN DBMS_SQL_TRANSLATOR.DROP_PROFILE( profile_name => 'tsql_application'); END;
158.6.5 ENABLE_ERROR_TRANSLATION Procedure
This procedure enables or disables a custom translation of an Oracle error code in a SQL translation profile.
Syntax
DBMS_SQL_TRANSLATOR.ENABLE_ERROR_TRANSLATION ( profile_name IN VARCHAR2, sql_text IN CLOB, enable IN BOOLEAN DEFAULT TRUE);
Parameters
Table 158-12 ENABLE_ERROR_TRANSLATION Procedure Parameters
Parameter | Description |
---|---|
|
Name of profile |
|
SQL statement |
|
Enable or disable the translation |
Exceptions
Table 158-13 ENABLE_ERROR_TRANSLATION Procedure Exceptions
Exception | Description |
---|---|
|
Bad argument is passed to the PL/SQL interface |
|
User has insufficient privilege for the operation |
|
Profile owner does not exist |
|
Profile does not exist |
Examples
BEGIN DBMS_SQL_TRANSLATOR.ENABLE_ERROR_TRANSLATION( profile_name => 'tsql_application', sql_text => 'SELECT TOP 5 * FROM emp' enable => TRUE); END;
158.6.6 ENABLE_SQL_TRANSLATION Procedure
This procedure enables or disables a custom translation of a SQL statement in a SQL translation profile.
Syntax
DBMS_SQL_TRANSLATOR.ENABLE_SQL_TRANSLATION ( profile_name IN VARCHAR2, sql_text IN CLOB, enable IN BOOLEAN DEFAULT TRUE);
Parameters
Table 158-14 ENABLE_SQL_TRANSLATION Procedure Parameters
Parameter | Description |
---|---|
|
Name of profile |
|
SQL statement |
|
Enable or disable the translation |
Exceptions
Table 158-15 ENABLE_SQL_TRANSLATION Procedure Exceptions
Exception | Description |
---|---|
|
Bad argument is passed to the PL/SQL interface |
|
User has insufficient privilege for the operation |
|
Profile owner does not exist |
|
Profile does not exist |
Examples
BEGIN DBMS_SQL_TRANSLATOR.ENABLE_SQL_TRANSLATION( profile_name => 'tsql_application', sql_text => 'select top 5 * from emp', enable => TRUE); END;
158.6.7 EXPORT_PROFILE Procedure
This procedure exports the content of a SQL translation profile.
Syntax
DBMS_SQL_TRANSLATOR.EXPORT_PROFILE ( profile_name IN VARCHAR2, content OUT NOCOPY CLOB);
Parameters
Table 158-16 EXPORT_PROFILE Procedure Parameters
Parameter | Description |
---|---|
|
Name of profile |
|
Content of profile |
Exceptions
Table 158-17 EXPORT_PROFILE Procedure Exceptions
Exception | Description |
---|---|
|
Bad argument is passed to the PL/SQL interface |
|
User has insufficient privilege for the operation |
|
Profile owner does not exist |
|
Profile does not exist |
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;
158.6.8 IMPORT_PROFILE Procedure
This procedure imports the content of a SQL translation profile.
Syntax
DBMS_SQL_TRANSLATOR.IMPORT_PROFILE ( profile_name IN VARCHAR2, content IN CLOB);
Parameters
Table 158-18 IMPORT_PROFILE Procedure Parameters
Parameter | Description |
---|---|
|
Name of profile |
|
Content of profile |
Exceptions
Table 158-19 IMPORT_PROFILE Procedure Exceptions
Exception | Description |
---|---|
|
Bad argument is passed to the PL/SQL interface |
|
User has insufficient privilege for the operation |
|
Profile owner does not exist |
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;
158.6.9 REGISTER_ERROR_TRANSLATION Procedure
This procedure registers a custom translation of an Oracle error code and SQLSTATE
in a SQL translation profile.
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
Table 158-20 REGISTER_ERROR_TRANSLATION Procedure Parameters
Parameter | Description |
---|---|
|
Name of profile |
|
Oracle error code |
|
Translated error code |
|
Translated |
|
Enable or disable the translation |
Exceptions
Table 158-21 REGISTER_ERROR_TRANSLATION Procedure Exceptions
Exception | Description |
---|---|
|
Bad argument is passed to the PL/SQL interface |
|
User has insufficient privilege for the operation |
|
Profile owner does not exist |
|
Profile does not exist |
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;
158.6.10 REGISTER_SQL_TRANSLATION Procedure
This procedure registers a custom translation of a SQL statement in a SQL translation profile.
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
Table 158-22 REGISTER_SQL_TRANSLATION Procedure Parameters
Parameter | Description |
---|---|
|
Name of profile |
|
SQL statement |
|
Translated SQL statement |
|
Enable or disable the translation |
Exceptions
Table 158-23 REGISTER_SQL_TRANSLATION Procedure Exceptions
Exception | Description |
---|---|
|
Bad argument is passed to the PL/SQL interface |
|
User has insufficient privilege for the operation |
|
Profile owner does not exist |
|
Profile does not exist |
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
isNULL
, 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;
158.6.11 SET_ATTRIBUTE Procedure
This procedure sets an attribute of a SQL translation profile.
Syntax
DBMS_SQL_TRANSLATOR.SET_ATTRIBUTE ( profile_name IN VARCHAR2, attribute_name IN VARCHAR2, attribute_value IN VARCHAR2;)
Parameters
Table 158-24 SET_ATTRIBUTE Procedure Parameters
Parameter | Description |
---|---|
|
Name of profile |
|
Name of attribute |
|
Value of attribute |
Exceptions
Table 158-25 SET_ATTRIBUTE Procedure Exceptions
Exception | Description |
---|---|
|
Bad argument is passed to the PL/SQL interface |
|
User has insufficient privilege for the operation |
|
Profile owner does not exist |
|
Profile does not exist |
Usage Notes
See Constants
158.6.12 SQL_HASH Function
This procedure computes the hash value of a SQL statement in the session's SQL translation profile.
Syntax
DBMS_SQL_TRANSLATOR.SQL_HASH ( sql_text IN CLOB) RETURN NUMBER DETERMINISTIC;
Parameters
Table 158-26 SQL_HASH Function Parameters
Parameter | Description |
---|---|
|
SQL statement |
Return Values
Returns hash value of the SQL statement in the SQL translation profile
Exceptions
Table 158-27 SQL_HASH Function Exceptions
Exception | Description |
---|---|
|
Bad argument is passed to the PL/SQL interface |
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;
158.6.13 SQL_ID Function
This procedure computes the SQL identifier of a SQL statement in a SQL translation profile.
Syntax
DBMS_SQL_TRANSLATOR.SQL_ID ( sql_text IN CLOB) RETURN VARCHAR2 DETERMINISTIC;
Parameters
Table 158-28 SQL_ID Function Parameters
Parameter | Description |
---|---|
|
SQL statement |
Return Values
Returns the SQL ID of the SQL statement in the SQL translation profile
Exceptions
Table 158-29 SQL_ID Function Exceptions
Exception | Description |
---|---|
|
Bad argument is passed to the PL/SQL interface |
Examples
DECLARE sqltext CLOB; sqlid VARCHAR2(13); BEGIN sqltext := 'SELECT TOP 1 * FROM emp'; sqlid := DBMS_SQL_TRANSLATOR.SQL_ID (sqltext); END;
158.6.14 TRANSLATE_ERROR Procedure
This procedure translates an Oracle error code and an ANSI SQLSTATE using the session's SQL translation profile
Syntax
DBMS_SQL_TRANSLATOR.TRANSLATE_ERROR ( error_code IN PLS_INTEGER, translated_code OUT PLS_INTEGER, translated_sqlstate OUT NOCOPY VARCHAR2);
Parameters
Table 158-30 TRANSLATE_ERROR Procedure Parameters
Parameter | Description |
---|---|
|
Oracle error code |
|
Translated error code |
|
Translated |
Exceptions
Table 158-31 TRANSLATE_ERROR Procedure Exceptions
Exception | Description |
---|---|
|
Bad argument is passed to the PL/SQL interface |
|
User has insufficient privilege for the operation |
|
Profile owner does not exist |
|
Profile does not exist |
|
No translation of the SQL statement or error code is found |
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;
158.6.15 TRANSLATE_SQL Procedure
This procedure translates a SQL statement using a SQL translation profile.
Syntax
DBMS_SQL_TRANSLATOR.TRANSLATE_SQL ( sql_text IN CLOB, translated_text OUT NOCOPY CLOB);
Parameters
Table 158-32 TRANSLATE_SQL Procedure Parameters
Parameter | Description |
---|---|
|
SQL statement |
|
Translated SQL statement |
Exceptions
Table 158-33 TRANSLATE_SQL Procedure Exceptions
Exception | Description |
---|---|
|
Bad argument is passed to the PL/SQL interface |
|
User has insufficient privilege for the operation |
|
Profile owner does not exist |
|
Profile does not exist |
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;