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)
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;
SQL> grant execute on DBMS_ADVANCED_REWRITE to scott;
SQL> create table sales_table (
item_id number primary key,
price number (10,2)
2 3 4
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);
SQL> select price + (price * 0.035) as tax from sales_table;
‘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
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%