Very often, with packaged vendor applications we do not have access to the source code, but would like to customise the application to some specific requirements or in many cases would like an optimised piece of SQL code to be executed which is actually sub-optimal when run directly via the vendor application. We can use DBMS_ADVANCED_REWRITE for this.
This feature is available in Oracle 10g Release 2 (maybe Release 1 as well)
Scenario:
Application calculates tax based on product price – earlier used to be 3.5% of the price – now has changed to 5% of the product price because of government regulations.
Instead of making code changes to application we can direct Oracle to execute a totally different query when a particular query is executed
SQL> grant query rewrite to scott;
Grant succeeded.
SQL> grant execute on DBMS_ADVANCED_REWRITE to scott;
Grant succeeded.
SQL> create table sales_table (
item_id number primary key,
price number (10,2)
);
2 3 4
Table created.
SQL> insert into sales_table values (1, 14.95);
insert into sales_table values (2, 17.50);
insert into sales_table values (3, 21.35);
commit;
SQL> select price + (price * 0.035) as tax from sales_table;
TAX
———-
15.47325
18.1125
22.09725
SQL> begin
sys.dbms_advanced_rewrite.declare_rewrite_equivalence
‘new_tax’,
‘select price + (price * 0.035) as tax from sales_table’, >>>> Old query run internally by application
‘select price + (price * 0.05) as tax from sales_table’, >>>> New query automatically run by application
false);
end;
PL/SQL procedure successfully completed.
SQL> select price + (price * 0.035) as tax from sales_table; >>> now calculates using new value of 5% instead of 3.5%
TAX
———-
15.6975
18.375
22.4175
(
Last Update: June 22, 2009