In my previous post, I tried to use views and macros together as an equivalent to parameterized views. Unfortunately, my generic macro can’t handle bind variables, which are crucial because in almost all cases they are the parameters! [UPDATE 2025-03-04: the comment below from Iudith Mentzel made me realize that the generic macro works fine, there was a bug in my most recent tests. Sorry about that! I’ll keep this post up as a tribute to Iudith’s comment :-)]
Here is an alternative, which is really just a variant of the solution published by Andrej Pashchenko: see https://blog.sqlora.com/en/parameterized-views-with-sql-macros-part-2/
As before, I’ll concentrate on these advantages of views:
- We can include them in queries anywhere a table could appear.
- The
*_DEPENDENCIESviews show the “dependency chain” between them and other objects.
Here is a super simple example:
create or replace view dept_emp as
select DNAME, LOC, EMPNO, ENAME, JOB
from dept
left join emp using(deptno);
select name, type, referenced_name, referenced_type
from user_dependencies where name = 'DEPT_EMP';
NAME TYPE REFERENCED_NAME REFERENCED_TYPE
DEPT_EMP VIEW DEPT TABLE
DEPT_EMP VIEW EMP TABLE
Now, what if we want to filter on DEPTNO? We can just copy the query from the view and add a filter:
create or replace function dept_emp_macro(p_deptno number)
return varchar2 sql_macro is
begin
return '
select DNAME, LOC, EMPNO, ENAME, JOB
from dept
left join emp using(deptno)
WHERE DEPTNO = P_DEPTNO';
end dept_emp_macro;
/
select * from dept_emp_macro(10);
DNAME LOC EMPNO ENAME JOB
ACCOUNTING NEW YORK 7839 KING PRESIDENT
ACCOUNTING NEW YORK 7782 CLARK MANAGER
ACCOUNTING NEW YORK 7934 MILLER CLERK
select name, type, referenced_name, referenced_type
from user_dependencies where name = 'DEPT_EMP_MACRO';
NAME TYPE REFERENCED_NAME REFERENCED_TYPE
DEPT_EMP_MACRO FUNCTION STANDARD PACKAGE
With the macro, we have a parameterized query but we have lost the information about the dependency chain in USER_DEPENDENCIES, so the second advantage of views is lost. Not only that, but the first advantage is limited as well: currently SQL macros inside WITH clauses are not permitted, and WITH clauses inside SQL macros cannot use scalar parameters!
My proposal [UPDATE 2025-03-04: see my previous post for my preferred solution]
As with Andrej’s solution, the trick is to write a view that works, but can be changed to inject macro parameters that will act as bind variables. I want the changed code to be as much like the view as possible, so the view uses existing columns as placeholders for the macro parameters and I simply put a “P_” prefix in front. Of course, the names in the view and in the macro have to be coordinated.
Here’s the view. It uses two different approaches to filter the rows of the tables I access. The view works and all the code is tested, even though no rows are filtered yet.
create or replace view DEPT_EMP_PV as
select DNAME, LOC, EMPNO, ENAME, JOB
from (
select DEPTNO, DNAME, LOC from DEPT
where NVL(/*<PARAM*/DNAME/*PARAM>*/, DNAME) = DNAME
)
join (
select DEPTNO, EMPNO, ENAME, JOB
from EMP E
where /*<PARAM*/E.JOB/*PARAM>*/ is null
or E.JOB = /*<PARAM*/E.JOB/*PARAM>*/
)
using(DEPTNO);
In the view, I compare each column to itself. In the macro, I replace each column in a PARAM sandwich by the macro parameter that follows the same naming convention. Note that the “E.” part is ignored.
create or replace function DEPT_EMP_SQM ( -- The function name is specific
P_VIEW in DBMS_TF.TABLE_T,
P_DNAME in varchar2 default null, -- specific parameter
P_JOB in varchar2 default null -- specific parameter
) return clob sql_macro is
L_ROW DEPT_EMP_PV%rowtype; -- unused but establishes dependency
L_QUERY long;
begin
select text into L_QUERY
from ALL_VIEWS
where OWNER = nvl(trim('"' from P_VIEW.SCHEMA_NAME), user)
and VIEW_NAME = trim('"' from P_VIEW.TABLE_NAME);
L_QUERY := REGEXP_REPLACE(
L_QUERY,
'\/\*<PARAM\*\/\s*(\S+\.)?(\S+)\s*\/\*PARAM>\*\/',
'P_\2'
);
DBMS_OUTPUT.PUT_LINE(L_QUERY);
return L_QUERY;
end DEPT_EMP_SQM;
/
Here is the actual text returned by the macro to the Oracle optimizer:
select DNAME, LOC, EMPNO, ENAME, JOB
from (
select DEPTNO, DNAME, LOC from DEPT
where NVL(P_DNAME, DNAME) = DNAME
)
join (
select DEPTNO, EMPNO, ENAME, JOB
from EMP E
where P_JOB is null
or E.JOB = P_JOB
)
using(DEPTNO)
To call the macro: select * from DEPT_EMP_SQM (DEPT_EMP_PV, :dname); for example.
Why do I make you type the name of the view? Because that establishes a dependency between your query and the view, so if the view changes the query will be invalidated and reparsed. I also place a reference to the view inside the macro (see line 6 above), so that the *_DEPENDENCIES views show the relationship between the macro and the view.
Posts on SQL table macros
- SQL table macros 01: a moving target?
- SQL table macros 02: “polymorphic” is not “dynamic”
- SQL table macros 3: parameter strangeness
- SQL table macros 4: distribute into equal parts
- SQL table macros 5: dynamic but dangerous!
- SQL table macros 6: SQM_UTIL package
- SQL table macros 7: Select excluding
- SQL table macros 8: Print table
- SQL table macros 9: just pivot!
- SQL table macros 10: JUST_PIVOT examples
- SQL table macros 11: JUST_PIVOT for 21c (and 19c)
- SQL table macros 12: compare tables or queries
- SQL table macros 13: compare with primary key
- SQL table macros 14: parameterized views
- SQL table macros 15: parameterized views revisited
An Oracle Programmer 
