A BRIEF PRIMER: VIEWS
Views are a great feature of the database, they are stored SQL queries that can be reused again and again without the need for rewriting the entire code. If a view has a filter in it however, and maybe you want to see the same resulting columns that the view generates but filtered for a different set of data, then you would have to write a different view! Imagine having to do this multiple times whenever you have to filter for a different data set, very quickly this becomes cumbersome and not very flexible! This is the challenge with views; they cannot accept a parameter. Enter SQL Macros; they are like views(TABLE macros) but can be passed parameters and return different results by running the same macro every time with different parameters. In this blog post, I will attempt to explain a bit about this feature and how they can help solve real problems.
What is all the fuzz about anyway?
When I first read about this feature, I was really excited and right away figured this is something that an really help me. Consider the data set in my previous post about Solving complex BOMS using hierarchical queries. I can use a SQL macro to simplify some of my queries. But before I delve into that example let’s look a bit at the technical documentation for this feature
The oracle database pl/sql language reference states that “The SQL_MACRO clause marks a function as a SQL macro which can be used as either a scalar expression or a table expression.” The difference?
- SCALAR expressions can be used in SELECT list, WHERE/HAVING, GROUP BY/ORDER BY clauses
- TABLE expressions used in a FROM-clause
Additionally, A SQL macro referenced in a view is always processed with the view owner’s privileges. The authid parameter cannot be specified for SQL Macros.
Some important points to note:
- A SQL macro cannot appear in a virtual column expression, functional index, editioning view or materialized view.
- They must return a string (
VARCHAR2,CHAR, orCLOB). - A
SCALARmacro cannot appear inFROMclause of a query table expression. It can appear wherever PL/SQL functions are allowed, for example in the select list, theWHEREclause, and theORDER BYclause. - Macros are always implicitly deterministic, meaning they return the same output for the same input, even though the
DETERMINISTICkeyword cannot be explicitly used. - Type methods cannot be annotated with
SQL_MACRO.
WHY NOT JUST USE PL/SQL?
Now if you’re like me and read this and wonder “Well you can create functions( regular and pipelined table functions ) to do the same thing” there’s one key thing about SQL macros that make them very cool
It says on the docs
Many
SCALARmacros can instead be written as standard PL/SQL functions, which can be called directly from a SQL statement. The PL/SQL function is automatically converted into a semantically equivalent SQL expression by the SQL Transpiler. This converted SQL expression is used during execution, replacing the call to the original PL/SQL function.Transpilation can improve performance by removing the need to switch between the SQL runtime and the PL/SQL runtime. Where possible, transpilation is performed automatically on any PL/SQL function called from a SQL statement (unless the feature has been explicitly disabled).
https://docs.oracle.com/en/database/oracle/oracle-database/26/lnpls/SQL_MACRO-clause.html
By using SQL macros, we can reduce context switching between the SQL and PL/SQL context which can have an impact on performance and thus SQL Macros provide a big boost in that regard. They also make the reusable SQL code completely transparent to the Optimizer – and that brings big benefits! It makes it possible for the optimizer to transform the original code for efficient execution because the underlying query inside the macro function can be merged into outer query.
SQL macros thus provide us with simplicity, clarity and faster execution.
SQL Macros in Action
As i mentioned earlier in my previous post about BOMs, I have a bom_table with data like the following
SQL> select * from
2* bom_table;
PK_SEQ ROOT_PARENT END_ITEM PARENT_PART PART_NUMBER BOM_LEVEL PART_LEAD_TIME
_________ ______________ ___________ ______________ ______________ ____________ _________________
1 CAR BODY CAR BODY 1 3
2 CAR BODY BODY DOOR 2 2
3 CAR BODY DOOR WINDOW 3 1
4 CAR BODY WINDOW BOLT 4 1
5 CAR ENGINE CAR ENGINE 1 4
6 CAR ENGINE ENGINE CYLINDER 2 2
7 CAR ENGINE CYLINDER PISTON 3 3
8 CAR ENGINE PISTON SCREW 4 1
9 CAR ENGINE CYLINDER VALVE 3 4
10 CAR ENGINE VALVE SPRING 4 1
11 CAR FRAME CAR FRAME 1 2
12 CAR FRAME FRAME SUPPORT 2 2
13 BIKE FRAME BIKE FRAME 1 3
14 BIKE FRAME FRAME PEDAL 2 2
15 BIKE FRAME PEDAL BOLT 3 1
16 BIKE HANDLE BIKE HANDLE 1 2
17 BIKE HANDLE HANDLE GEAR 2 3
17 rows selected.I can write the below query to “explode” the Bill of Material for a car
SQL> SELECT part_number, parent_part, root_parent|| sys_connect_by_path(part_number, ' <- ') part_buildup, bom_level
2 FROM bom_table
3 WHERE root_parent = 'CAR'
4 START WITH bom_level = 1
5 CONNECT by prior part_number = parent_part
6 ORDER BY LEVEL
7* ;
PART_NUMBER PARENT_PART PART_BUILDUP BOM_LEVEL
----------- ----------- ---------------------------------------------- ---------
BODY CAR CAR <- BODY 1
FRAME CAR CAR <- FRAME 1
ENGINE CAR CAR <- ENGINE 1
SUPPORT FRAME CAR <- FRAME <- SUPPORT 2
SUPPORT FRAME CAR <- FRAME <- SUPPORT 2
CYLINDER ENGINE CAR <- ENGINE <- CYLINDER 2
DOOR BODY CAR <- BODY <- DOOR 2
WINDOW DOOR CAR <- BODY <- DOOR <- WINDOW 3
VALVE CYLINDER CAR <- ENGINE <- CYLINDER <- VALVE 3
PISTON CYLINDER CAR <- ENGINE <- CYLINDER <- PISTON 3
BOLT WINDOW CAR <- BODY <- DOOR <- WINDOW <- BOLT 4
SCREW PISTON CAR <- ENGINE <- CYLINDER <- PISTON <- SCREW 4
SPRING VALVE CAR <- ENGINE <- CYLINDER <- VALVE <- SPRING 4
Elapsed: 00:00:00.154
13 rows selected.
I can make this query a view and reuse it over and over again
CREATE or REPLACE VIEW car_bom_view
AS
SELECT part_number, parent_part, root_parent|| sys_connect_by_path(part_number, ' <- ') part_buildup, bom_level
FROM bom_table
WHERE root_parent = 'CAR'
START WITH bom_level = 1
CONNECT by prior part_number = parent_part
ORDER BY LEVEL ;
View CAR_BOM_VIEW created.
Elapsed: 00:00:00.017
select * from car_bom_view ;
PART_NUMBER PARENT_PART PART_BUILDUP BOM_LEVEL
----------- ----------- ---------------------------------------------- ---------
BODY CAR CAR <- BODY 1
FRAME CAR CAR <- FRAME 1
ENGINE CAR CAR <- ENGINE 1
SUPPORT FRAME CAR <- FRAME <- SUPPORT 2
SUPPORT FRAME CAR <- FRAME <- SUPPORT 2
CYLINDER ENGINE CAR <- ENGINE <- CYLINDER 2
DOOR BODY CAR <- BODY <- DOOR 2
WINDOW DOOR CAR <- BODY <- DOOR <- WINDOW 3
VALVE CYLINDER CAR <- ENGINE <- CYLINDER <- VALVE 3
PISTON CYLINDER CAR <- ENGINE <- CYLINDER <- PISTON 3
BOLT WINDOW CAR <- BODY <- DOOR <- WINDOW <- BOLT 4
SCREW PISTON CAR <- ENGINE <- CYLINDER <- PISTON <- SCREW 4
SPRING VALVE CAR <- ENGINE <- CYLINDER <- VALVE <- SPRING 4
Elapsed: 00:00:00.006
13 rows selected.
However If I want to see the BOM for a Bike this same view cannot do that! instead of creating a new view for the bike, I can write a TABLE MACRO to do both for me. The syntax is as below
CREATE OR REPLACE FUNCTION bom_display( root_parent varchar2) return varchar2 SQL_MACRO is
BEGIN
RETURN q'{
SELECT part_number, parent_part, root_parent|| sys_connect_by_path(part_number, ' <- ') part_buildup, bom_level
FROM bom_table
WHERE root_parent = bom_display.root_parent
START WITH bom_level = 1
CONNECT by prior part_number = parent_part
ORDER BY LEVEL
}';
END;
/
Function BOM_DISPLAY compiled
Elapsed: 00:00:00.025
I can now use this one Macro to return Bom explosions for both the BIKE and CAR as below
SELECT *
FROM total_sales(zip_code => '60332');
PART_NUMBER PARENT_PART PART_BUILDUP BOM_LEVEL
----------- ----------- ---------------------------------------------- ---------
BODY CAR CAR <- BODY 1
FRAME CAR CAR <- FRAME 1
ENGINE CAR CAR <- ENGINE 1
SUPPORT FRAME CAR <- FRAME <- SUPPORT 2
SUPPORT FRAME CAR <- FRAME <- SUPPORT 2
CYLINDER ENGINE CAR <- ENGINE <- CYLINDER 2
DOOR BODY CAR <- BODY <- DOOR 2
WINDOW DOOR CAR <- BODY <- DOOR <- WINDOW 3
VALVE CYLINDER CAR <- ENGINE <- CYLINDER <- VALVE 3
PISTON CYLINDER CAR <- ENGINE <- CYLINDER <- PISTON 3
BOLT WINDOW CAR <- BODY <- DOOR <- WINDOW <- BOLT 4
SCREW PISTON CAR <- ENGINE <- CYLINDER <- PISTON <- SCREW 4
SPRING VALVE CAR <- ENGINE <- CYLINDER <- VALVE <- SPRING 4
Elapsed: 00:00:00.020
13 rows selected.
------
SELECT *
FROM bom_display(root_parent => 'BIKE');
PART_NUMBER PARENT_PART PART_BUILDUP BOM_LEVEL
----------- ----------- -------------------------------- ---------
FRAME BIKE BIKE <- FRAME 1
HANDLE BIKE BIKE <- HANDLE 1
PEDAL FRAME BIKE <- FRAME <- PEDAL 2
PEDAL FRAME BIKE <- FRAME <- PEDAL 2
GEAR HANDLE BIKE <- HANDLE <- GEAR 2
BOLT PEDAL BIKE <- FRAME <- PEDAL <- BOLT 3
BOLT PEDAL BIKE <- FRAME <- PEDAL <- BOLT 3
Elapsed: 00:00:00.001
7 rows selected.
No need to re-write code or multiple views to do the same thing for different parameters. This example is not the most cumbersome query, but this just goes to show how code can be made re-usable, flexible and with great benefits to performance.
DO EVEN MORE WITH MACROS
The above example merely scratches the surface of this feature; It only explains using the table macro, let’s see another example using the SCALAR Macro
Say I want to find out for any part it’s root parent ie what part that componnent is used to build, I can write a scalar macro to do that such as the following
create or replace function show_root_parent(p_part VARCHAR2)
return varchar2 sql_macro(scalar)
is
begin
return q'{ SELECT distinct root_parent FROM bom_table
WHERE part_number = show_root_parent.p_part }';
end;
Function SHOW_ROOT_PARENT compiled
Elapsed: 00:00:00.004
select part_number, show_root_parent(part_number) root_parent
from bom_table
where part_number = 'PISTON' ;
PART_NUMBER ROOT_PARENT
----------- -----------
PISTON CAR
Elapsed: 00:00:00.001
1 rows selected.
Macros are extremely powerful and have a wide range of uses including:
- scalar macro definitions can be incorporated into package bodies. see example
- Macros can also accept more than one parameter, use analytic functions, or accept no parameters at all.
- A single Macro can also perform action against multiple tables.
- They are also allowed in DML (INSERT, UPDATE, DELETE) and some DDL like CTAS
viewing Macros
Macros can be viewed using the SQL_MACRO column in the DBA/ALL/USER _PROCEDURES views.
select uo.object_type,
up.sql_macro,
up.object_name,
up.procedure_name
from user_procedures up
join user_objects uo on up.object_id = uo.object_id
where up.sql_macro != 'NULL'
order by uo.object_type, up.sql_macro, up.object_name, up.procedure_name;
OBJECT_TYPE SQL_MACRO OBJECT_NAME PROCEDURE_NAME
----------- --------- ------------------ --------------
FUNCTION SCALAR SHOW_ROOT_PARENT
FUNCTION TABLE BOM_DISPLAY
FUNCTION TABLE SEL_EMP_JOB_DEPT
FUNCTION TABLE TOTAL_SALES
Elapsed: 00:00:00.188
4 rows selected.
You can see the code for a sql macro in user_source
select line, text
from user_source
where name = 'BOM_DISPLAY'
order by line;
LINE TEXT
---- -------------------------------------------------------------------------------------------------------------------
1 FUNCTION bom_display( root_parent varchar2) return varchar2 SQL_MACRO is
2 BEGIN
3 RETURN q'{
4 SELECT part_number, parent_part, root_parent|| sys_connect_by_path(part_number, ' <- ') part_buildup, bom_level
5 FROM bom_table
6 WHERE root_parent = bom_display.root_parent
7 START WITH bom_level = 1
8 CONNECT by prior part_number = parent_part
9 ORDER BY LEVEL
10 }';
11 END;
Elapsed: 00:00:00.120
11 rows selected.
They can also be displayed using the dbms_metadata package
Sooo… A REALLY COOL FEATURE OR WHAT?
Since I first read about them, I love this feature and I do see myself using them quite a bit given all the performance benefits and flexibility they offer. Whilst not being the saving grace for all situations as sometimes you do need your views, functions and procedures, in the case where SQL macros can be used they can be a really big help. There is a good demo on FREESQL on this feature: https://freesql.com/worksheet?tutorial=sql-macros-creating-parameterised-views-mgEdl9
References:
https://docs.oracle.com/en/database/oracle/oracle-database/26/lnpls/SQL_MACRO-clause.html
https://docs.oracle.com/en/database/oracle/oracle-database/26/lnpls/SQL_MACRO-clause.html
Cheers
