SQL Translation Framework

In diesem Beitrag wollen wir ein Feature der Oracle Datenbank vorstellen, das eigentlich zur Unterstützung von Migrationen von anderen Datenbanken auf die Oracle Datenbank gedacht ist. Aber man kann dieses Feature durchaus noch für andere spannende Dinge verwenden. Es handelt sich um das SQL Translation Framework, das im SQL Translation and Migration Guide beschrieben ist. Die Idee ist, SQL-Statements, die für andere Datenbanken entwickelt wurden und von Oracle nicht direkt ausgeführt werden können, noch vor dem Parsing so anzupassen, dass Oracle die Statements verarbeiten kann. Im Prinzip wird ein Regelwerk erstellt, wie Statements anzupassen sind, damit sie ausgeführt werden können. Man kann sich das wie ein Suchen und Ersetzen im SQL-Text vorstellen. Da es damit aber eben möglich ist, beliebige Statements vor dem Parsing anzupassen, kann man das SQL Translation Framework noch für viel mehr verwenden.  

Um das SQL Translation Framework verwenden zu können, wird das Package DBMS_SQL_TRANSLATOR verwendet. Um es verwenden zu können, wird das Recht "CREATE SQL TRANSLATION PROFILE" benötigt.

SQL> show user
USER ist "SYS"

SQL> grant create sql translation profile to schema_owner;

Grant succeeded.

Die Funktionsweise kann am einfachsten folgendes Beispiel verdeutlichen. Es wird ein Translation Profile angelegt und dessen Eigenschaften definiert. Im einfachsten Fall wie hier handelt es sich um SQL, das nicht von einer fremden Datenbank ausgeht und einfach einen SQL-Text durch einen anderen ersetzt.

SQL> show user
USER ist "SCHEMA_OWNER"

SQL> begin
  2    dbms_sql_translator.create_profile(profile_name => 'MMI_SQLTRANS_TEST');
  3    dbms_sql_translator.set_attribute(
  4      profile_name    => 'MMI_SQLTRANS_TEST',
  5      attribute_name  => dbms_sql_translator.ATTR_FOREIGN_SQL_SYNTAX,
  6      attribute_value => dbms_sql_translator.ATTR_VALUE_FALSE
  7    );
  8    dbms_sql_translator.register_sql_translation(
  9      profile_name    => 'MMI_SQLTRANS_TEST',
 10      sql_text        => 'select sysdate from dual',
 11      translated_text => 'select current_date from dual'
 12    );
 13  end;
 14  /

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> alter session set sql_translation_profile=schema_owner.MMI_SQLTRANS_TEST;

Session wurde geändert.

SQL> select sysdate from dual;

CURRENT_
--------
11.10.21

Wenn man nun im Cache nachsieht, erkennt man die Funktionsweise. Das originale Statement ist dort nicht zu finden, sondern nur das durch das SQL Translation Profile geänderte Statement.

SQL> show user
USER is "SYS"
SQL> select parsing_schema_name, sql_text from v$sql where sql_text like 'select%from dual' and parsing_schema_name = 'SCHEMA_OWNER';

PARSING_SCHEMA_NAME  SQL_TEXT
-------------------- ------------------------------------------------------------
SCHEMA_OWNER         select current_date from dual

In einem vorigen Blogeintrag haben wir bereits den Unterschied von SYSDATE und CURRENT_DATE erläutert. Im ersten Beispiel wurde schon SYSDATE durch CURRENT_DATE ersetzt. Aber sicher möchte man nicht für jedes einzelne Statement ein Profile anlegen. Zu diesem Zweck kann man ein eigenes Package erstellen, das die Übersetzung übernimmt. Das Package benötigt zwei Prozeduren, eine für das tatsächliche Übersetzen  und eine für die Fehlerbehandlung. Dieses Package wird dann im SQL Translation Profile hinterlegt. Das Beispiel fokussiert sich nun auf die Übersetzung, die Fehlerbehandlung wird hier nicht benötigt. Im Prinzip wird das Package nichts anderes als ein Suchen und Ersetzen durchführen und alle Vorkommnisse von SYSDATE und SYSTIMESTAMP entsprechend durch CURRENT_DATE und CURRENT_TIMESTAMP ersetzen.

SQL> create or replace package pkg_translate_date is
  2    procedure translate_sql(
  3      sql_text        in  CLOB,
  4      translated_text out CLOB
  5    );
  6    procedure translate_error(
  7      error_code          in  BINARY_INTEGER,
  8      translated_code     out BINARY_INTEGER,
  9      translated_sqlstate out varchar2
 10    );
 11  end;
SQL> /

Package wurde erstellt.

SQL> create or replace package body pkg_translate_date is
  2    procedure translate_sql(
  3      sql_text        in  CLOB,
  4      translated_text out CLOB
  5    ) is
  6    begin
  7      translated_text := regexp_replace(sql_text       , 'SYSDATE'     , 'CURRENT_DATE'     , 1, 0, 'i');
  8      translated_text := regexp_replace(translated_text, 'SYSTIMESTAMP', 'CURRENT_TIMESTAMP', 1, 0, 'i');
  9    end;
 10    procedure translate_error(
 11      error_code          in  BINARY_INTEGER,
 12      translated_code     out BINARY_INTEGER,
 13      translated_sqlstate out varchar2
 14    ) is
 15    begin
 16      null;
 17    end;
 18  end;
 19  /

Package Body wurde erstellt.

 Nun wird das Translation Profile erst einmal wieder entfernt und neu angelegt, so dass das Package nun Verwendung findet.

SQL> begin
  2    dbms_sql_translator.drop_profile(profile_name => 'MMI_SQLTRANS_TEST');
  3    dbms_sql_translator.create_profile(profile_name => 'MMI_SQLTRANS_TEST');
  4    dbms_sql_translator.set_attribute(
  5      profile_name    => 'MMI_SQLTRANS_TEST',
  6      attribute_name  => dbms_sql_translator.ATTR_FOREIGN_SQL_SYNTAX,
  7      attribute_value => dbms_sql_translator.ATTR_VALUE_FALSE
  8    );
  9    dbms_sql_translator.set_attribute(
 10      profile_name    => 'MMI_SQLTRANS_TEST',
 11      attribute_name  => dbms_sql_translator.ATTR_TRANSLATOR,
 12      attribute_value => 'SCHEMA_OWNER.PKG_TRANSLATE_DATE'
 13    );
 14    dbms_sql_translator.register_sql_translation(
 15      profile_name    => 'MMI_SQLTRANS_TEST',
 16      sql_text        => 'select sysdate from dual',
 17      translated_text => 'select current_date from dual'
 18    );
 19  end;
SQL> /

PL/SQL-Prozedur erfolgreich abgeschlossen.

Wird nun die Verwendung des Translation Profiles wieder aktiviert, kann das Ergebnis überprüft werden.

SQL> alter session set sql_translation_profile=schema_owner.MMI_SQLTRANS_TEST;

Session wurde geändert.

SQL> select sysdate from all_tables where rownum=1;

CURRENT_
--------
11.10.21

SQL> select systimestamp from all_tables where rownum=1;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
11.10.21 12:37:41,563635 +02:00

Im Cache taucht dann wiederum nur der geänderte SQL-Text auf.

SQL> select parsing_schema_name, sql_text from v$sql where parsing_schema_name = 'SCHEMA_OWNER' and sql_text like '%CURRENT%all_tables%';

PARSING_SCHEMA_NAME  SQL_TEXT
-------------------- ------------------------------------------------------------
SCHEMA_OWNER         select CURRENT_DATE from all_tables where rownum=1
SCHEMA_OWNER         select CURRENT_TIMESTAMP from all_tables where rownum=1

Somit existiert eine einfache Prozedur, die unabhängig von den Anwendungen die Verwendung der korrekten Datumsfunktion forciert. Es sind keine Anpassungen an den Applikationen nötig und auch Änderungen an den SQL-Statements an sich setzen die Übersetzung nicht außer Funktion. Die Aktivierung des SQL Translation Profile kann beispielsweise ein Logon Trigger übernehmen. 

Soll ein SQL Translation Profile von mehreren Datenbankbenutzern verwendet werden, so müssen entsprechende Berechtigungen vergeben werden. Das Package DBMS_SQL_TRANSLATOR läuft immer mit den Berechtigungen des angemeldeten Benutzers, der benötigt daher also Rechte zum Ausführen des Packages sowie Rechte auf das Profile an sich.

SQL> show user
USER ist "SCHEMA_OWNER"
SQL> grant execute on PKG_TRANSLATE_DATE to schema_user;

Benutzerzugriff (Grant) wurde erteilt.

SQL> grant use on sql translation profile MMI_SQLTRANS_TEST to schema_user;

Benutzerzugriff (Grant) wurde erteilt.

Allerdings genügt das noch nicht. der SCHEMA_USER kann das Translation Profile noch nicht aktivieren. Vielmehr muss er dem Besitzer des Packages bzw. des Profiles noch erlauben, seine Statements zu übersetzen.

SQL> show user
USER ist "SCHEMA_USER"
SQL> alter session set sql_translation_profile=schema_owner.MMI_SQLTRANS_TEST;
ERROR:
ORA-01031: Nicht ausreichende Berechtigungen


SQL> grant translate sql on user schema_user to schema_owner;

Benutzerzugriff (Grant) wurde erteilt.

SQL> alter session set sql_translation_profile=schema_owner.MMI_SQLTRANS_TEST;

Session wurde geändert.

Das SQL Translation Framework ist ein mächtiges Werkzeug zur Beeinflussung von SQL-Statements. Man kann damit die Statements beliebig verändern, wie das Beispiel gezeigt hat. Somit kann es unter anderem auch für Bugfixing oder Performancethemen eingesetzt werden indem die Statements vor der Ausführung entsprechend umgeschrieben werden. Der persönlichen Kreativität sind hier kaum Grenzen gesetzt. Aber natürlich kann es ebenso für den eigentlichen Einsatzzweck, zu Migrationen von anderen Systemen, effektiv verwendet werden.