SQL macros are ridiculously powerful and I think as time goes on, we are going to see more and more innovative usages of this super cool tech. It may seem a strange analogy, but I liken them to analytic window functions from way back in version 8.1.6, in that the syntax arrives, people scratch their head for a while wondering how it might be useful, and suddenly BOOM, thousands of previously complex problems are become trivially solved. I don’t think we have even touched the tip of the iceberg when it comes to SQL macros.However, because they are in essence a string “masequerading” as a SQL query, one facet of SQL macros you need to take care with is dependency tracking. For example, here’s a simple SQL macro that refers to the EMP table.
SQL> create or replace
2 function f1 return varchar2 sql_macro(table) is
3 begin
4 return q'{select empno, ename from emp where deptno = 10}';
5 end;
6 /
Function created.
If some maintenance work was planned for the EMP table, I might delve into DBA_DEPENDENCIES to see what might be impacted, and as you can see, the SQL Macro is nowhere to be seen.
SQL> select referenced_owner||'.'||referenced_name dep
2 from dba_dependencies
3 where name = 'F1';
DEP
-------------------------------------------------------------------
SYS.STANDARD
I must admit, I’d love to the have RELIES_ON style clause for such cases, but unfortunately that is limited to the result cache.
SQL> create or replace
2 function f1 return varchar2 sql_macro(table) result_cache relies_on (emp) is
3 begin
4 return q'{select empno, ename from emp where deptno = 10}';
5 end;
6 /
Warning: Function created with compilation errors.
SQL> sho err
Errors for FUNCTION F1:
LINE/COL ERROR
-------- -----------------------------------------------------------------
1/10 PLS-00778: PARTITION BY, ORDER BY, CLUSTER BY, or PARALLEL_ENABLE
are not allowed for SQL macros
So I thought I’d propose a couple of options that you might want to consider if you’d like to track dependencies in the usual way.One option would be to create a view which references the macro. You could come up with whatever naming scheme you want to indicate that this is probably not a view that is used in normally operations, and similarly, I’ve thrown in a WHERE 1=0 so that even if someone did execute a statement against it, there is no chance of any resource impact since the optimizer will short circuit the entire querty into a NO-OP due to the always false condition.
SQL> create or replace
2 view never_to_be_used as
3 select * from f1() where 1=0;
View created.
Even though the view references the macro, which in turn is just a string, in order to parse out the columns for the view definition, you can see that we have picked up the desired dependency information against the EMP table
SQL> select referenced_owner||'.'||referenced_name dep
2 from dba_dependencies
3 where name = 'NEVER_TO_BE_USED';
DEP
-----------------------------------------------------------
SCOTT.F1
SCOTT.EMP
Another option you could consider is to add an appropriate dependency into the SQL macro function itself. In the example below, I’ve put a reference to EMP via a ROWTYPE variable, which is unused in the function.
SQL> create or replace
2 function f1 return varchar2 sql_macro(table) is
3 l_noop emp%rowtype;
4 begin
5 return q'{select empno, ename from emp where deptno = 10}';
6 end;
7 /
Function created.
SQL> select referenced_owner||'.'||referenced_name dep
2 from dba_dependencies
3 where name = 'F1';
DEP
-------------------------------------------------------------------
SYS.STANDARD
SCOTT.EMP
Of course, one of the cool things with macros is that even the table can be provided at run time, so knowing the appropriate dependencies at compilation time would not be possible. For example:
SQL> create or replace
2 function f1(tname dbms_tf.table_t) return varchar2 sql_macro(table) is
3 begin
4 return q'{select empno, ename from tname where deptno = 10}';
5 end;
6 /
Function created.
SQL> select * from f1(emp);
EMPNO ENAME
---------- ----------
7782 CLARK
7839 KING
7934 MILLER
However, for such macros, DBMS_TF would be a dependency, which could serve as a trigger that any references elsewhere in the code to the function F1 need to be checked for dependency analysis.
SQL> select referenced_owner||'.'||referenced_name dep
2 from dba_dependencies
3 where name = 'F1';
DEP
--------------------------------------------------------
SYS.STANDARD
PUBLIC.DBMS_TF
If can think of other means to track the dependencies easily, please drop me a comment.
Learning is not a spectator sport 
Got some thoughts? Leave a comment