Simple example
The specification of the PL/SQL package that translates SQL statements …
create or replace package renes_sql_translator as
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
);
end renes_sql_translator;
/
… and its body:
create or replace package body renes_sql_translator as
procedure translate_sql(
sql_text in clob,
translated_text out clob
)
is begin
translated_text := 'select * from (' || sql_text || ') where rownum < 2';
end translate_sql;
procedure translate_error(
error_code in binary_integer,
translated_code out binary_integer,
translated_sqlstate out varchar2
)
is begin
translated_code := 42;
translated_sqlstate :='what?';
end translate_error;
end renes_sql_translator;
/
Create a translation profile. Drop it, if it already exists
declare
profile_name varchar2(32) := 'renes_sql_translation_profile';
begin
begin
dbms_sql_translator.drop_profile(profile_name);
exception when others then
if sqlcode = -24254 then null; end if;
end;
dbms_sql_translator.create_profile(profile_name);
dbms_sql_translator.set_attribute(
profile_name,
dbms_sql_translator.attr_translator,
'renes_sql_translator'
);
end;
/
Use the new profile in the current
session:
alter session set sql_translation_profile = renes_sql_translation_profile;
Check the translation of an SQL statement.
declare
sql_new clob;
begin
dbms_sql_translator.translate_sql(
'select * from dba_users',
sql_new
);
dbms_output.put_line('Translated SQL is:');
dbms_output.put_line(' ' || sql_new);
end;
/
Yes, this seems to be necessary in order to actually translate the text of an SQL statement. Setting event 10601 to level 32 specifies that the following SQL statement is one with foreign syntax (rather than native syntax). (TODO: This strange thing possibly goes away when setting attr_foreign_sql_syntax to false).
alter session set events = '10601 trace name context forever, level 32';
Execute an SQL statement, let Oracle translate it and return the translated statement's result set:
select * from dba_objects;
TODO
select * from dba_sql_translation_profiles;
select * from dba_objects where object_type = 'SQL TRANSLATION PROFILE';
alter session set sql_translation_profile = …;
alter session set events = '10601 trace name context forever, level 32';
grant create sql translation profile to rene;