All about Database Administration, Tips & Tricks

Most of time developers writes multiple procedures or pl/sql code with relevant conditions and blocks to simplify operations , but the complexity grows and number of joins increases the operations of SQL Engine and PLSQL engine has to involved more and do more context switching at the cost of performance. In Oracle Database 20c, SQL Macros solve this problem by allowing SQL expressions and table functions to be replaced by calls to stored procedures which return a string literal to be inserted in the SQL we want to execute. It's an incredibly simple concept and one that C and Rust programmers will be familiar with. The following trivial example shows it in action.

Some example from blogs.oracle.com

First, let's create a tables and insert a few rows.

CREATE TABLE(id integer, name varchar2(30), item_type varchar2(30), price float );
insert into line_items values (1, 'Red Red Wine', 'ALCOHOL', 15.6)
insert into line_items values (2, 'Its Cold Out There Heater', 'RADIATOR', 200.49);
insert into line_items values (3, 'How Sweet It Is Cake', 'FOOD', 4.56);

The SQL below calculates the value added tax on rows in our LINE_ITEMS table

select id,
  case
    when item_type = 'ALCOHOL' then round(1.2 * price, 2)
    when item_type = 'SOLAR PANEL' then round(1.05 * price, 2)
    when item_type = 'RADIATOR' then round(1.05 * price, 2)
    else price end as total_price_with_tax
from line_items; 

However in Oracle Database 20c we can simplify it by creating a function with the new SQL_MACRO keyword and returning a string.

create or replace function total_price_with_tax(the_price float, the_item_type varchar)
  return varchar2 SQL_MACRO(SCALAR) is
begin
  return q'[case
    when item_type = 'ALCOHOL' then round(1.2 * price, 2)
    when item_type = 'SOLAR PANEL' then round(1.05 * price, 2)
    when item_type = 'RADIATOR' then round(1.05 * price, 2)
    else price end as total_price_with_tax]';
end; 

We can then simply reference the SQL Macro inside of a select statement. The SQL that's executed is exactly the same as the original SQL Statement without the overhead of a context switch each time the row is fetched to execute our function.

 
SQL > select id, total_price_with_tax(price, item_type) from line_items;

  ID   TOTAL_PRICE_WITH_TAX(PRICE,ITEM_TYPE) 
  ------------------------------------------
   1                                   18.72 
   2                                210.5145 
   3                                    4.56