Folks like me who research table macros would sometimes like them to be truly dynamic, such as when PIVOTing rows into columns. Here’s one way to achieve that goal, with multiple warnings about what this can do to your database!
Simple PIVOT example
select * from (
select dname, job, ename
from dept join emp using(deptno)
)
pivot(
listagg(ename ,',') within group(order by ename)
for job in (
'ANALYST' as ANALYST,
'CLERK' as CLERK,
'MANAGER' as MANAGER,
'PRESIDENT' as PRESIDENT,
'SALESMAN' as SALESMAN
)
);
| DNAME | ANALYST | CLERK | MANAGER | PRESIDENT | SALESMAN |
|---|---|---|---|---|---|
| SALES | JAMES | BLAKE | ALLEN,MARTIN,TURNER,WARD | ||
| RESEARCH | FORD,SCOTT | ADAMS,SMITH | JONES | ||
| ACCOUNTING | MILLER | CLARK | KING |
This looks fine to me, but only because I know beforehand what five JOBs are in the EMP table. They are listed in the lines that I have highlighted above.
What happens if I insert an employee with a new job title? Nothing: my query will not show that new data at all. If I want to show all the jobs all the time, I first need a query to get an up-to-date job list.
insert into emp
select 9999, 'SMITH', 'PM', 7698, sysdate, 99999, 9999, 30 from dual;
select listagg(distinct '''' || job || ''' as ' || job, ',
')within group(order by job)
from emp;
'ANALYST' as ANALYST,
'CLERK' as CLERK,
'MANAGER' as MANAGER,
'PM' as PM,
'PRESIDENT' as PRESIDENT,
'SALESMAN' as SALESMAN
If I plug the new list into my original query, I will see the new data.
Why not a SQL table macro?
This table macro will do exactly what I just did: get the list of jobs, then plug that list into the PIVOT query.
create or replace function emp_by_dname_job
return varchar2 sql_macro is
L_sql varchar2(4000) :=
'select * from (
select dname, job, ename
from dept join emp using(deptno)
)
pivot(listagg(ename ,'','') within group(order by ename) for job in(
#JOBLIST#
))';
l_joblist varchar2(4000);
begin
select listagg(distinct '''' || job || ''' as ' || job, ',
') within group(order by job)
into l_joblist
from emp;
l_sql := replace(l_sql, '#JOBLIST#', l_joblist);
dbms_output.put_line(l_sql);
return l_sql;
end;
/
select * from emp_by_dname_job( );
-- output from dbms_output.put_line(l_sql);
select * from (
select dname, job, ename
from dept join emp using(deptno)
)
pivot(listagg(ename ,',') within group(order by ename) for job in(
'ANALYST' as ANALYST,
'CLERK' as CLERK,
'MANAGER' as MANAGER,
'PM' as PM,
'PRESIDENT' as PRESIDENT,
'SALESMAN' as SALESMAN
))
| DNAME | ANALYST | CLERK | MANAGER | PM | PRESIDENT | SALESMAN |
|---|---|---|---|---|---|---|
| SALES | JAMES | BLAKE | SMITH | ALLEN,MARTIN,TURNER,WARD | ||
| RESEARCH | FORD,SCOTT | ADAMS,SMITH | JONES | |||
| ACCOUNTING | MILLER | CLARK | KING |
That looks like it works, but that is a mirage. In fact, the job list was generated at query parse time. If the data changes, the query will stay the same. Let’s promote Smith:
update emp set job = 'VP' where job = 'PM';
select * from emp_by_dname_job();
| DNAME | ANALYST | CLERK | MANAGER | PM | PRESIDENT | SALESMAN |
|---|---|---|---|---|---|---|
| SALES | JAMES | BLAKE | ALLEN,MARTIN,TURNER,WARD | |||
| RESEARCH | FORD,SCOTT | ADAMS,SMITH | JONES | |||
| ACCOUNTING | MILLER | CLARK | KING |
The old PM job is there and the new VP job is not.
Dynamic yes, but at a price!
The table macro only executes when the query is “hard parsed”. If the optimizer has already parsed the query, it will find a cursor in the shared pool that provides an execution plan; if that cursor is “shareable”, the optimizer will execute that plan instead of parsing the query again.
To make the macro “dynamic”, we have no choice but to require a hard parse before every execution. One way to do this is by doing a flashback query using AS OF SCN. Such queries are always reparsed. The question remains, as of what SCN? Since we really just want current data, we can call dbms_flashback.get_system_change_number to stay current.
create or replace function emp_by_dname_job_dyn
return varchar2 sql_macro is
L_sql varchar2(4000) :=
'with pivoted as (
select * from (
select dname, job, ename
from dept join emp using(deptno)
)
pivot(listagg(ename ,'','') within group(order by ename) for job in(
#JOBLIST#
))
)
select * from pivoted as of scn dbms_flashback.get_system_change_number';
l_joblist varchar2(4000);
begin
select listagg(distinct '''' || job || ''' as ' || job, ',
') within group(order by job)
into l_joblist
from emp;
l_sql := replace(l_sql, '#JOBLIST#', l_joblist);
return l_sql;
end;
/
select * from emp_by_dname_job_dyn();
| DNAME | ANALYST | CLERK | MANAGER | PRESIDENT | SALESMAN | VP |
|---|---|---|---|---|---|---|
| SALES | JAMES | BLAKE | ALLEN,MARTIN,TURNER,WARD | SMITH | ||
| RESEARCH | FORD,SCOTT | ADAMS,SMITH | JONES | |||
| ACCOUNTING | MILLER | CLARK | KING |
delete from emp where ename = 'SMITH';
select * from emp_by_dname_job_dyn();
| DNAME | ANALYST | CLERK | MANAGER | PRESIDENT | SALESMAN |
|---|---|---|---|---|---|
| SALES | JAMES | BLAKE | ALLEN,MARTIN,TURNER,WARD | ||
| RESEARCH | FORD,SCOTT | ADAMS | JONES | ||
| ACCOUNTING | MILLER | CLARK | KING |
Danger!
Whenever the optimizer hard parses a query, it creates a cursor in the shared pool that provides the execution plan. In our case, the same SELECT statement is parsed more than once, so the optimizer creates one “parent cursor” and subsequent “child cursors”, all with the same SQL_ID. If users ran this query constantly, the shared pool would be flooded with child cursors, which would cause all sorts of performance problems! Be very, very careful about allowing this technique in production. I would never even consider it outside of data warehouses.
Fortunately, this technique is only available if the DBA allows it. It requires the following privileges:
EXECUTEonDBMS_FLASHBACKFLASHBACKon all the tables accessed by the query.
Posts on SQL table macros
- SQL table macros 1: a moving target?
- SQL table macros 2: “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
An Oracle Programmer 