Chapter 38. DBMS_SQL

Chapter 38. DBMS_SQL_TRANSLATOR

Table of Contents

38.2.1. CREATE_PROFILE
38.2.2. DEREGISTER_ERROR_TRANSLATION
38.2.3. DEREGISTER_SQL_TRANSLATION
38.2.4. DROP_PROFILE
38.2.5. ENABLE_ERROR_TRANSLATION
38.2.6. ENABLE_SQL_TRANSLATION
38.2.7. REGISTER_ERROR_TRANSLATION
38.2.8. REGISTER_SQL_TRANSLATION
38.2.9. SET_ATTRIBUTE
38.2.10. SQL_HASH
38.2.11. SQL_ID
38.2.12. TRANSLATE_ERROR
38.2.13. TRANSLATE_SQL

This chapter briefly introduces the DBMS_SQL_TRANSLATOR package, and describes how to use the procedures of the package.

38.1. Overview

DBMS_SQL_TRANSLATOR creates and manages SQL translation profiles. Since it executes at the caller's privilege level, the caller must have the EXECUTE privilege for the translator package to call its procedure through functions in this package.

The translator package must be a PSM package with the following 2 procedures.

PROCEDURE TRANSLATE_SQL
(
    sql_text        IN  CLOB,
    translated_text OUT CLOB
);
 
PROCEDURE TRANSLATE_ERROR
(
    error_code          IN  PLS_INTEGER,
    translated_code     OUT PLS_INTEGER,
    translated_sqlstate OUT VARCHAR2
);

The following describes the parameters of the translator package procedures.

Parameter	Description
sql_text	SQL statement to translate.
translated_text	Translated SQL statement.
error_code	Tibero error code to translate.
translated_code	Translated Tibero error code.
translated_sqlstate	Translated SQLSTATE.

The following describes constants defined in the DBMS_SQL_TRANSLATOR package. The constants are used to set attribute values of a SQL translation profile.

ATTR_TRANSLATOR

Sets a translator package in the form of [schema.]package_name. This attribute is not set by default. (Default value: none)
```
ATTR_TRANSLATOR CONSTANT VARCHAR2(30) := 'TRANSLATOR'
```

ATTR_TRANSLATE_NEW_SQL

Option to translate a new SQL statement (or error code) using the translator package. (Default value: TRUE)

Value	Description
TRUE	Translate a new SQL statement (or error code), which is not registered as a custom translation, using the translator package and register the result as a custom translation. Custom translation can be done automatically by the translator, or manually by the user using functions such as register_sql_translation.
FALSE	Throw an error by default if the custom translation is not registered.

Value

Description

TRUE

Translate a new SQL statement (or error code), which is not registered as a custom translation, using the translator package and register the result as a custom translation.

Custom translation can be done automatically by the translator, or manually by the user using functions such as register_sql_translation.

FALSE

Throw an error by default if the custom translation is not registered.

ATTR_TRANSLATE_NEW_SQL CONSTANT VARCHAR2(30) := 'TRANSLATE_NEW_SQL'

ATTR_RAISE_TRANSLATION_ERROR

Option to throw an error if there is no custom translation and the translator package cannot be used. (Default value: FALSE)
```
ATTR_RAISE_TRANSLATION_ERROR CONSTANT VARCHAR2(30) := 'RAISE_TRANSLATION_ERROR'
```

ATTR_VALUE_TRUE

Sets an attribute value to TRUE.

ATTR_VALUE_TRUE CONSTANT VARCHAR2(30) := 'TRUE'

ATTR_VALUE_FALSE

Sets an attribute value to FALSE.

ATTR_VALUE_FALSE CONSTANT VARCHAR2(30) := 'FALSE'

38.2. Procedures and Functions

This section describes the procedures and functions provided by the DBMS_SQL_TRANSLATOR package, in alphabetical order.

38.2.1. CREATE_PROFILE

Creates a SQL translation profile, which is a schema object with a separate namespace.

Details about the CREATE_PROFILE procedure are as follows:

Prototype

DBMS_SQL_TRANSLATOR.CREATE_PROFILE 
(
    profile_name IN VARCHAR
);

Parameter

Parameter Description
profile_name Name of the profile to create in the form of [schema.]name.

Parameter	Description
profile_name	Name of the profile to create in the form of [schema.]name.

38.2.2. DEREGISTER_ERROR_TRANSLATION

Unregisters a custom translation registered for an error code.

Details about the DEREGISTER_ERROR_TRANSLATION procedure are as follows:

Prototype

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

Parameter

Parameter Description
profile_name Profile name.
error_code Error code for which translation is to be unregistered.

38.2.3. DEREGISTER_SQL_TRANSLATION

Unregisters a custom translation registered for a SQL statement.

Details about the DEREGISTER_SQL_TRANSLATION procedure are as follows:

Prototype

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

Parameter

Parameter Description
profile_name Profile name.
sql_text SQL statement for which translation is to be unregistered.

38.2.4. DROP_PROFILE

Drops a SQL translation profile.

Details about the DROP_PROFILE procedure are as follows:

Prototype

DBMS_SQL_TRANSLATOR.DROP_PROFILE 
(
     profile_name IN VARCHAR
);

Parameter

Parameter Description
profile_name Name of the profile to drop.

Parameter	Description
profile_name	Name of the profile to drop.

38.2.5. ENABLE_ERROR_TRANSLATION

Enables a custom translation registered for an error code. This allows the translation to be used in the translator.

Details about the ENABLE_ERROR_TRANSLATION procedure are as follows:

Prototype

DBMS_SQL_TRANSLATOR.ENABLE_ERROR_TRANSLATION 
(
    profile_name    IN VARCHAR,
    error_code      IN PLS_INTEGER,
    enable          IN BOOLEAN     DEFAULT TRUE
);

Parameter

Parameter Description
profile_name Profile name.
error_code Error code.
enable Option to enable the translation.

38.2.6. ENABLE_SQL_TRANSLATION

Enables a custom translation registered for a SQL statement. This allows the translation to be used in the translator.

Details about the ENABLE_SQL_TRANSLATION procedure are as follows:

Prototype

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

Parameter

Parameter Description
profile_name Profile name.
sql_text SQL statement.
enable Option to enable the translation.

38.2.7. REGISTER_ERROR_TRANSLATION

Registers a custom translation to an error code. When translating an error code through a translation profile, it first checks if there is a custom translation registered for the code and uses it if found. Otherwise, the translator package function is executed.

If a custom translation is already registered, it is replaced by the new translation.

Details about the REGISTER_ERROR_TRANSLATION procedure are as follows:

Prototype

DBMS_SQL_TRANSLATOR.REGISTER_ERROR_TRANSLATION 
(
    profile_name        IN VARCHAR,
    error_code          IN PLS_INTEGER,
    translated_code     IN PLS_INTEGER DEFAULT NULL,
    translated_sqlstate IN VARCHAR     DEFAULT NULL,
    enable              IN BOOLEAN     DEFAULT TRUE
);

Parameter

Parameter	Description
profile_name	Profile name.
error_code	Tibero error code to translate.
translated_code	Translated error code.
translated_sqlstate	Translated SQLSTATE.
enable	Option to enable the translation.

38.2.8. REGISTER_SQL_TRANSLATION

Registers a custom translation to a SQL statement. When translating a SQL statement through a translation profile, it first checks if there is a custom translation registered for the code and uses it if found. Otherwise, the translator package function is executed.

If a custom translation is already registered, it is replaced by the new translation.

Details about the REGISTER_SQL_TRANSLATION procedure are as follows:

Prototype

DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION 
(
    profile_name        IN VARCHAR,
    sql_text            IN CLOB,
    translated_text     IN CLOB        DEFAULT NULL,
    enable              IN BOOLEAN     DEFAULT TRUE
);

Parameter

Parameter Description
profile_name Profile name.
sql_text SQL statement to translate.
translated_text Translated SQL statement.
enable Option to enable the translation.

38.2.9. SET_ATTRIBUTE

Sets an attribute value of a SQL translation profile.

Details about the SET_ATTRIBUTE procedure are as follows:

Prototype

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

Parameter

Parameter Description
profile_name Profile name.
attribute_name Name of the attribute to set.
attribute_value Attribute value.

38.2.10. SQL_HASH

Calculates a hash value of a SQL statement.

Details about the SQL_HASH function are as follows:

Prototype

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

Parameter

Parameter Description
sql_text SQL statement whose hash value is to be calculated.

Parameter	Description
sql_text	SQL statement whose hash value is to be calculated.

38.2.11. SQL_ID

Returns the SQL_ID of a SQL statement.

Details about the SQL_ID function are as follows:

Prototype

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

Parameter

Parameter Description
sql_text SQL statement whose SQL_ID is to be returned.

Parameter	Description
sql_text	SQL statement whose SQL_ID is to be returned.

38.2.12. TRANSLATE_ERROR

Translates an error code. A SQL translation profile to use must be predefined using the 'ALTER SESSION SET SQL_TRANSLATION_PROFILE' command before executing this procedure.

Details about the TRANSLATE_ERROR procedure are as follows:

Prototype

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

Parameter

Parameter Description
error_code Tibero error code to translate.
translated_code Translated error code.
translated_sqlstate Translated SQLSTATE.

Parameter	Description
error_code	Tibero error code to translate.
translated_code	Translated error code.
translated_sqlstate	Translated SQLSTATE.

38.2.13. TRANSLATE_SQL

Translates a SQL statement. A SQL translation profile to use must be predefined using the 'ALTER SESSION SET SQL_TRANSLATION_PROFILE' command before executing this procedure.

Details about the TRANSLATE_SQL procedure are as follows:

Prototype

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

Parameter

Parameter Description
sql_text SQL statement to translate.
translated_text Translated SQL statement.

Prev		Next
Chapter 37. DBMS_SQLTUNE	Home	Chapter 39. DBMS_STATS

Parameter	Description
profile_name	Profile name.
attribute_name	Name of the attribute to set.
attribute_value	Attribute value.