Author: Maria Colgan

Database views have been used for decades to help simplify both ad-hoc queries and reporting for developers, analysts, and end-users. But the problem with defining views is they tend to be either too specific (not easily reused) or not specific enough (too generic to be performant).

Imagine we need a view to help developers quickly find the details about orders waiting to ship, which have a total value of $100 or more. How would you create such a view?

Would you create a particular view that returns only the columns needed for this scenario with restrictive where clause predicates that limit the orders to only those that have not shipped and have a value greater than $100?

 CREATE OR REPLACE VIEW orders_waiting 
AS 
SELECT i.order_id, i.product_id, i.price, i.description 
FROM   orders o, order_items i
WHERE  o.order_status > 6   -- orders waiting to ship
AND    o.order_total >= 100
AND    o.order_id = i.order_id;

Or do you create a more generic view that could be used for other reports and queries, even though it won’t be as performant as the dedicated view above?

 CREATE OR REPLACE VIEW orders_waiting 
AS 
SELECT o.order_status, o.order_total,
       o.cust_id, o.order_date, o.rep,       
       o.order_mode, o.promotion_id, 
       i.* 
FROM   orders o, order_items i
WHERE  o.order_id = i.order_id;

Regardless of your approach, you will compromise either reusability or performance.

Starting in Oracle Database 19c (19.7), you no longer have to compromise, as Oracle introduced parameterized views or SQL Table Macros.

SQL table macros are expressions found in a FROM clause that acts as a polymorphic or parameterized view. Let’s look at how this can help us with the view we need to create to find the details about orders waiting to ship, which have a total value of $100 or more.

Creating a SQL Macro is similar to creating a PL/SQL function with an additional SQL_MACRO clause. The SQL MACRO clause can take an argument SCALAR (available from 21c onwards) or TABLE (19c onwards), but if you leave it blank, it defaults to a TABLE macro.

CREATE OR REPLACE FUNCTION orders_waiting_to_ship
RETURN CLOB sql_macro AS
 stmt CLOB;
BEGIN
 ...
 RETURN stmt;
END orders_waiting_to_ship;
/

A SQL Macro always returns the view you define as text (VARCHAR, CHAR, or CLOB). The database will resolve that view definition and makes it part of the SQL statement that calls the SQL Macro. I’m capturing the view definition in a CLOB using the variable stmt above.

Let’s add the text for the view and the parameters we want to pass.

CREATE OR REPLACE FUNCTION orders_waiting_to_ship(order_value INTEGER)
RETURN CLOB sql_macro AS
 stmt CLOB;
BEGIN
 stmt := '
  SELECT i.*
  FROM   orders o, order_items i
  WHERE  o.order_status > 6
  AND    o.order_total >= order_value
  AND    o.order_id = i.order_id';
 
 RETURN stmt;
END orders_waiting_to_ship;
/
 
FUNCTION ORDERS_WAITING_TO_SHIP compiled

In the code example above, I’m using the more selectivity view definition, but instead of specifying the order_total to be $100 or higher, I’m going to parameterize the value of the order. That way, we can reuse this SQL Table Macro regardless of what order_value is needed. You will notice I have also added a parameter to the function called order_value, which the user will pass in when they call the function.

You will also notice that my function ORDERS_WAITING_TO_SHIP compiled without errors. This doesn’t mean my view definition or syntax is correct. You will only see an error at runtime. Remember, a SQL TABLE Macro produces your view definition as text that the optimizer will insert into the SQL statement that calls it.

It’s straightforward to call a SQL Table Macro. The Table Macro goes in the FROM clause of the SQL statement. In the example below, I’m calling our orders_waiting_to_Ship SQL Macro and passing it the value 100.

 SELECT *
FROM orders_waiting_to_ship(100) 
ORDER BY order_id;
 
   ORDER_ID    PRODUCT_ID    PRICE DESCRIPTION    
___________ _____________ ________ ______________ 
      10110            10       23 lipstick       
      10110             7       17 Lip gloss      
      10110             8       30 Mascarra
      10110             1       35 Foundation
      20209             9       15 Blusher        
      20209            10       23 lipstick  
      20209             1       35 Foundation
      20209             2       32 Coverup
      30307             7       17 Lip gloss      
      30307             8       30 Mascarra       
      30307             1       35 Foundation       
      30307             2       32 Coverup

We could take it a step further and generalize our SQL Table Macro a little more by parameterizing the order_status and the order_value; that way, our developers can use it to check the status of shipped orders and orders waiting to ship.

 CREATE OR REPLACE FUNCTION orders_chk(order_value INTEGER, stat INTEGER)
RETURN CLOB sql_macro AS
 stmt CLOB;
BEGIN
 stmt := '
 SELECT i.*
 FROM   orders o, order_items i
 WHERE  o.order_status = orders_chk.stat
 AND    o.order_total >= orders_chk.order_value
 AND    o.order_id = i.order_id';
 
 RETURN stmt;
END orders_chk;
/
 
FUNCTION ORDERS_CHK compiled
 
SELECT *
FROM orders_chk(10,6)
ORDER BY order_id;
 
   ORDER_ID    PRODUCT_ID    PRICE DESCRIPTION     ......
___________ _____________ ________ ______________ __________ 
      10110            10       23 lipstick        ......
      10110             7       17 Lip gloss       ......
      10110             8       30 Mascarra        ......
      10110             1       35 Foundation      ......
      10111             9       15 Blusher         ......         
      10112             1       35 Foundation      ......
      10113             1       17 Lip gloss       ......

There are several restrictions on SQL TABLE Macros you should be aware of:

• • When used directly in a SQL statement, the SQL_MACRO annotation is disallowed with RESULT_CACHE, PARALLEL_ENABLE, and PIPELINE.
  • A SQL macro always runs with invoker rights.
  • SQL macros in views are always executed with the privileges of the view owner.
  • SQL macros can’t be used in virtual column expression, function-based indexes, editioning views, or materialized views.
  • SQL macros can’t be used in type methods.