For a long time, we got a facility called SQL Translator for
dynamically translating non-Oracle SQL dialect to Oracle dialect; In addition
to that, it is also possible to patch a client application by dynamically
changing the sql text at runtime.
Will show how this patching can done using SQL Translator feature. Will
start with a package.
demo@XEPDB1>
create or replace package date_translator
2 is
3
procedure translate_sql(sql_text in clob ,
4
translated_text out clob );
5
6
procedure translate_error( error_code in binary_integer ,
7 translated_code out
binary_integer,
8 translated_sqlstate out
varchar2 );
9 end;
10 /
Package
created.
These API’s must be hardcoded, must one should be “translate_sql” and “translate_error”
methods and must take the above set of parameters.
And here is the package body.
demo@XEPDB1>
create or replace package body date_translator
2 is
3
procedure translate_sql(sql_text in clob ,
4
translated_text out clob )
5
as
6
begin
7
translated_text := regexp_replace(sql_text,
8
'SYSDATE','add_months( current_date, 12*50*-1)',1,0,'i');
9
translated_text := regexp_replace(translated_text,
10
'systimestamp','current_timestamp',1,0,'i');
11
end;
12
13
procedure translate_error( error_code in binary_integer ,
14 translated_code out
binary_integer,
15 translated_sqlstate out
varchar2 )
16
as
17
begin
18
null ;
19 end;
20 end;
21 /
Package body
created.
What we are going to do is take input sqltext and having “SYSDATE”
function call and translate into fifty years ago (older) from the client date.
Similarly change the sqltext having “SYSTIMESTAMP” function calls into client
timestamp (i.e. dynamically change server timestamp to client timestamp)
Here is how the package is get used in the profile.
demo@XEPDB1>
begin
2
dbms_sql_translator.create_profile('DEMO_PROFILE');
3
4
dbms_sql_translator.set_attribute(
5
profile_name=>'DEMO_PROFILE',
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=>'DEMO_PROFILE',
11
attribute_name=> dbms_sql_translator.ATTR_TRANSLATOR,
12
attribute_value=> 'demo.date_translator' );
13
14 end;
15 /
PL/SQL
procedure successfully completed.
So we created a profile and as it should be applied for all sql (and
not just for foreign (non-oracle) sql dialects), now we have the new thing
called what translator we have to use, that is our new package
“date_translator”. So when a sql comes in it calls this package
'demo.date_translator' – and perform the necessary translation - because we set
this attribute “dbms_sql_translator.ATTR_TRANSLATOR” for this profile.
Let us see this in action.
demo@XEPDB1>
select deptno,count(*)
2 from
emp
3
where hiredate < sysdate
4
group by deptno;
DEPTNO COUNT(*)
------
----------
30
6
10
3
20
5
demo@XEPDB1>
alter session set sql_translation_profile=DEMO_PROFILE;
Session
altered.
demo@XEPDB1>
select deptno,count(*)
2 from
emp
3
where hiredate < sysdate
4
group by deptno;
DEPTNO COUNT(*)
------
----------
20
2
so post the profile got applied and translated, we got two option to
see the final sql text, either pulling the plan cursor cache.
demo@XEPDB1>
alter session set statistics_level=all;
Session
altered.
demo@XEPDB1>
select deptno,count(*)
2 from
emp
3
where hiredate < sysdate
4
group by deptno;
DEPTNO COUNT(*)
------
----------
20
2
demo@XEPDB1>
select * From table( dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID 8u8mq3pb9pjhy, child number 1
-------------------------------------
select deptno,count(*)
from emp where hiredate < add_months(
current_date,
12*50*-1) group
by deptno
Plan hash
value: 4067220884
---------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows
| A-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | |
1 |00:00:00.01 |
| 1 |
HASH GROUP BY | |
1 | 2 | 1 |00:00:00.01 |
|* 2 |
TABLE ACCESS FULL| EMP | 1 |
3 | 2 |00:00:00.01 |
---------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
2 -
filter("HIREDATE"<ADD_MONTHS(CURRENT_DATE,-600))
Alternatively, by using “expand_sql_text” method from “dbms_utility”
API’s.
demo@XEPDB1>
variable x clob
demo@XEPDB1>
declare
2
l_txt long;
3
begin
4
l_txt := 'select deptno,count(*)
5 from emp
6 where hiredate <
sysdate
7 group by deptno ';
8
dbms_utility.expand_sql_text( l_txt,:x );
9 end;
10 /
PL/SQL
procedure successfully completed.
demo@XEPDB1>
print x
X
--------------------------------------------------------------------------------
SELECT
"A1"."DEPTNO" "DEPTNO",COUNT(*)
"COUNT(*)" FROM "DEMO"."EMP" "A1" WHERE
"
A1"."HIREDATE"<ADD_MONTHS(CURRENT_DATE,12*50*(-1)) GROUP BY
"A1"."DEPTNO"
Therefore, with this we got two great things with SQL Translation
profile.
·
Grab an entire sql and replace that with another
sql, but that still need not to be a non-oracle sql’s too.
·
Using translation package, we can pick and
choose elements of incoming sql and dynamically replace them with whatever we
like (in this example we replace sysdate and systimestamp with current_date and
current_timestamp).
No comments:
Post a Comment