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.
Excellent