Loading....

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

 

 

(

Please follow and like us:

Last Update: June 22, 2009  

June 22, 2009 200 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?