One thing I learned early on when I was getting to know SQL macros is that we cannot replace each and every part of a SQL statement with a macro. In the same way, working with table SQL macros, we cannot represent just any part of the result string by referencing parameters inside it. Sometimes we need to use string concatenation. But why it behaves this way? In this post I will look at the available trace information about SQL macro expansion during hard parse and try to understand its impact on this restriction. After that we’ll also look how to overcome it and what to consider.
In the previous posts about finding the final SQL and other information about SQM macro expansion in the CBO trace we have already seen how to access CBO trace. Let’s first change the function introduced there to find the final SQL to be able to additionally output the relevant pieces to show what happens during SQM expansion (Listing 1). You don’t need to understand the function to follow this post, it is just used to generate the output of SQM expansion, which is indeed interesting. I published it just in case you want to do some tests on your own. And you can find the explanations in the original post.
create or replace function sqm_trace_info (P_query in varchar2)
return sys.odcivarchar2list pipelined is
v_result varchar2(4000);
v_tracefile varchar2(100) := 'SQM_'||to_char(sysdate, 'HH24MISS');
v_dump_trace_cmd varchar2(500);
v_sql_id varchar2(20);
v_cur sys_refcursor;
v_child number;
v_trcline varchar2(32000);
begin
-- get sql_id for the query
v_sql_id := dbms_sql_translator.sql_id(p_query);
-- parse query
open v_cur for p_query;
select max(child_number) into v_child from sys.v_$sql where sql_id = v_sql_id;
close v_cur;
if v_child is not null then
v_dump_trace_cmd :=
replace(
replace(
replace( q'[
begin
DBMS_SQLDIAG.DUMP_TRACE( p_sql_id=>'#sql_id#'
, p_child_number=> '#child#'
, p_component=>'Compiler'
, p_file_id=>'#v_trace_identifier#');
end;]','#sql_id#',v_sql_id)
, '#v_trace_identifier#', v_tracefile)
, '#child#', v_child) ;
-- create a trace file
execute immediate v_dump_trace_cmd;
-- return the query to find the relevant line
v_result := q'[ select x.trcline from v$diag_trace_file_contents
match_recognize (
partition by trace_filename
order by line_number
measures payload as trcline
all rows per match
pattern (a | b nc*|c |f n)
define a as (payload like 'qksptfSQM_GetTxt(): Anonymous Block%')
, b as (payload like 'qksptfSQM_GetTxt(): Macro Text%')
, nc as (payload not like 'qksptfSQM_Template(): Template Text%')
, c as (payload like 'qksptfSQM_Template(): Template Text%')
, f as (payload like 'Final query after%')
) x
where trace_filename like '%#TRACEFILENAME#%'
]';
open v_cur for replace (v_result,'#TRACEFILENAME#', v_tracefile);
loop
fetch v_cur into v_trcline;
exit when v_cur%notfound;
pipe row(v_trcline);
end loop;
else
pipe row (replace(q'[The SQL_ID #sql_id# was not found!]', '#sql_id#', v_sql_id ));
end if;
end;
/
Listing 1: Function to show the content from CBO trace file about SQM expansion
Now let’s try to answer the question from the title of this post. Can I change the shape of the query using parameters? There is no definitive answer. I think, the way it is generally supposed to work, the answer would be NO! And here is why. Let’s define a table SQL macro as a UNION for two queries which use scalar parameters. Don’t expect any meaning behind this, it’ just a silly example. And then let’s use the function defined above to see what happens behind the scene.
SQL> create or replace function sqm_set_op(p_name1 varchar2, p_name2 varchar2)
return varchar2 sql_macro (table)
is
begin
return q'[SELECT * FROM emp WHERE ename = p_name1
UNION
SELECT * FROM emp WHERE ename = p_name2]';
end;
/
Function created.
SQL> select * from sqm_trace_info(q'[select * from sqm_set_op('SCOTT','KING')]')
COLUMN_VALUE
--------------------------------------------------------------------------------
qksptfSQM_GetTxt(): Anonymous Block
===================================
declare
begin
:macro_text := "SQM_SET_OP"(:3 ,:4 );
end;
qksptfSQM_GetTxt(): Macro Text
==============================
SELECT * FROM emp WHERE ename = p_name1
UNION
SELECT * FROM emp WHERE ename = p_name2
qksptfSQM_Template(): Template Text
===================================
select "SYS__$".*
from (select NULL "P_NAME1",NULL "P_NAME2" from SYS.DUAL) "SQM_SET_OP",
lateral(SELECT * FROM emp WHERE ename = p_name1
UNION
SELECT * FROM emp WHERE ename = p_name2) "SYS__$"
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "SYS__$"."EMPNO" "EMPNO",
"SYS__$"."ENAME" "ENAME",
"SYS__$"."JOB" "JOB",
"SYS__$"."MGR" "MGR",
"SYS__$"."HIREDATE" "HIREDATE",
"SYS__$"."SAL" "SAL",
"SYS__$"."COMM" "COMM",
"SYS__$"."DEPTNO" "DEPTNO"
FROM ( (SELECT "EMP"."EMPNO" "EMPNO",
"EMP"."ENAME" "ENAME",
"EMP"."JOB" "JOB",
"EMP"."MGR" "MGR",
"EMP"."HIREDATE" "HIREDATE",
"EMP"."SAL" "SAL",
"EMP"."COMM" "COMM",
"EMP"."DEPTNO" "DEPTNO"
FROM "SCOTT"."EMP" "EMP"
WHERE "EMP"."ENAME" = 'SCOTT')
UNION
(SELECT "EMP"."EMPNO" "EMPNO",
"EMP"."ENAME" "ENAME",
"EMP"."JOB" "JOB",
"EMP"."MGR" "MGR",
"EMP"."HIREDATE" "HIREDATE",
"EMP"."SAL" "SAL",
"EMP"."COMM" "COMM",
"EMP"."DEPTNO" "DEPTNO"
FROM "SCOTT"."EMP" "EMP"
WHERE "EMP"."ENAME" = 'KING')) "SYS__$";
Listing 2: Table SQL macro and its expansion
First, we see an anonymous block to run our macro passing parameters as binds – nothing special here in our simple case. Second, we can see the macro text returned from our SQL macro. And finally, there is a query we see under the section “Template Text”. It is not exactly the final query, but it is a smart way to check that all passed parameters are actually scalar. Look at the line 35. All scalar parameters are selected from dual in one subquery which has the same name as the SQL macro. These parameters are then passed to the actual query (i.e. exactly the return string from SQL macro) through a LATERAL join.
In this second query inside the LATERAL clause, no parameter substitution has to take place, here we have just parameter names. It is no problem if they are prefixed with the SQL macro function name, because the subquery is also named the same. The only place for substitution is this single subquery selecting from dual.
Now, what happens if we decided to make this macro more “generic” and pass the set operator we want to use as a parameter? That is, if we wanted to dynamically change UNION to UNION ALL or MINUS and so on. The Listing 3 shows this approach which is not working, of course.
SQL> create or replace function sqm_set_op_generic(p_name1 varchar2
, p_name2 varchar2
, p_operator varchar2)
return varchar2 sql_macro (table)
is
begin
return q'[SELECT * FROM emp WHERE ename = p_name1
p_operator
SELECT * FROM emp WHERE ename = p_name2]';
end;
Function created.
SQL> select * from sqm_set_op_generic('SCOTT','KING', 'UNION')
>> select * from sqm_set_op_generic('SCOTT','KING', 'UNION')
*
Error at line 15
ORA-64626: invalid SQL text returned from SQL macro:
ORA-00907: missing right parenthesis
Listing 3: Using a parameter otherwise than for a scalar expression fails
We cannot generate a CBO trace file in this case because the statement is not valid and not parseable at all. But we can suggest why this happens (Listing 4). We will have all three parameters in the first subquery and the lateral-subquery will reference the parameter P_OPERATOR just as the two other ones. Of course, this go wrong and we see exactly the same error message ORA-00907 that we got when calling the SQM.
>> select "SYS__$".*
from (select NULL "P_NAME1",NULL "P_NAME2", NULL "P_OPERATOR" from SYS.DUAL) "SQM_SET_OP2",
lateral(SELECT * FROM emp WHERE ename = p_name1 p_operator SELECT * FROM emp WHERE ename = p_name2) "SYS__$"
*
Error at line 3
ORA-00907: missing right parenthesis
Listing 4: The template SQL will fail if we just reference the parameter name.
Can we workaround this?
If we cannot just use the parameter name inside the result string for this purpose, then we can probably concatenate it? No! This is because scalar parameters which can hold text (VARCHAR2, CHAR, CLOB, RAW, JSON – just what I’ve tested) are intentionally NULL inside the SQM body. Unfortunately this is not clearly mentioned in the documentation but explained on various blogs, including this one. Listing 5 confirms this behavior once again. Note that I have already used MINUS after the first query and then again concatenated my parameter between the two queries. So if I call the macro using the value “UNION” for p_operator, I may expect it to fail because we will have MINUS and UNION next to each other. But this doesn’t happen, P_OPERATOR is just NULL and can’t even get it into the query – we only see MINUS in the result string and the template query.
SQL> create or replace function sqm_set_op_generic(p_name1 varchar2
, p_name2 varchar2
, p_operator varchar2)
return varchar2 sql_macro (table)
is
begin
return 'SELECT * FROM emp WHERE ename = p_name1 MINUS '
|| p_operator ||
'SELECT * FROM emp WHERE ename = p_name2';
end;
Function created.
SQL> select * from sqm_trace_info(q'[select * from sqm_set_op_generic('SCOTT','KING', 'UNION')]')
COLUMN_VALUE
-----------------------------------------------------------
...
qksptfSQM_GetTxt(): Macro Text
==============================
SELECT * FROM emp WHERE ename = p_name1 MINUS SELECT * FROM emp WHERE ename = p_name2
qksptfSQM_Template(): Template Text
===================================
select "SYS__$".*
from (select NULL "P_NAME1",NULL "P_NAME2",NULL "P_OPERATOR" from SYS.DUAL) "SQM_SET_OP_GENERIC",
lateral(SELECT * FROM emp WHERE ename = p_name1 MINUS SELECT * FROM emp WHERE ename = p_name2) "SYS__$"
Listing 5: String parameters are NULL and not suitable for concatenation
We cannot evaluate or use (including concatenation) such parameters directly in the body. The only thing we can do, is to put them into the result string. The real parameter values are only substituted after the execution of the macro. The idea behind this “nullifying” of parameters is to mitigate the risks of SQL injection. There is at least one known workaround, which was suggested by Stew Ashton and can also be found on this site: using collection data types. But before I introduce it again, I would like to emphasize it once again: if you decide to use this workaround and to bypass this protection (like on the picture illustrating this post 🙂 ), then you are yourself responsible for the risks! Check the input in you SQL macros!
The Listing 6 shows this approach. Just define your own collection or use some of the predefined ones such as for example sys.odcivarchar2list and declare the parameter to be of this type. You can then concatenate the parameter value and it will work. Don’t forget to sanitize your input!
SQL> create or replace type t_operator as varray(1) of varchar2(20);
2 /
Type T_OPERATOR compiled
SQL> create or replace function sqm_set_op_generic(p_name1 varchar2
2 , p_name2 varchar2
3 , p_operator t_operator)
4 return varchar2 sql_macro (table)
5 is
6 begin
7 if not regexp_like(trim(upper(p_operator(1))),
8 '^(UNION|MINUS|INTERSECT|EXCEPT){1}((\s)+ALL){0,1}$') then
9 raise_application_error(-20003,'Invalid set operator');
10 end if;
11
12 return 'SELECT * FROM emp WHERE ename = p_name1 '
13 || p_operator(1) ||
14 ' SELECT * FROM emp WHERE ename = p_name2';
15 end;
16 /
Function SQM_SET_OP_GENERIC compiled
SQL> select * from sqm_trace_info(q'[select * from sqm_set_op_generic('SCOTT','KING', t_operator('UNION'))]');
COLUMN_VALUE
-----------------------------------------------------
qksptfSQM_GetTxt(): Anonymous Block
===================================
declare
begin
:macro_text := "SQM_SET_OP_GENERIC"(:3 ,:4 ,"T_OPERATOR"('UNION'));
end;
qksptfSQM_GetTxt(): Macro Text
==============================
SELECT * FROM emp WHERE ename = p_name1 UNION SELECT * FROM emp WHERE ename = p_name2
qksptfSQM_Template(): Template Text
===================================
select "SYS__$".*
from (select NULL "P_NAME1",NULL "P_NAME2",NULL "P_OPERATOR" from SYS.DUAL) "SQM_SET_OP_GENERIC",
lateral(SELECT * FROM emp WHERE ename = p_name1 UNION SELECT * FROM emp WHERE ename = p_name2) "SYS__$"
Listing 6: Using collection data types at your own risk
The better approach would be in my opinion to make such generic SQL macro be a private function in a package body and offer multiple specific versions which don’t allow any additional parameters like in Listing 7. Once again, it doesn’t make any sense to define those macros with hard coded “select from emp” – it was just to illustrate the problem.
SQL> create or replace package sqm_set_ops as
2
3 function set_operator_union(p_name1 varchar2, p_name2 varchar2) return clob sql_macro (table);
4
5 function set_operator_union_all(p_name1 varchar2, p_name2 varchar2) return clob sql_macro (table);
6
7 end;
8 /
Package SQM_SET_OPS compiled
SQL>
SQL> create or replace package body sqm_set_ops as
2
3 function sqm_set_op_generic(p_name1 varchar2
4 , p_name2 varchar2
5 , p_operator t_operator)
6 return varchar2 sql_macro (table)
7 is
8 begin
9 if not regexp_like(trim(upper(p_operator(1))),
10 '^(UNION|MINUS|INTERSECT|EXCEPT){1}((\s)+ALL){0,1}$') then
11 raise_application_error(-20003,'Invalid set operator');
12 end if;
13
14 return 'SELECT * FROM emp WHERE ename = p_name1 '
15 || p_operator(1) ||
16 ' SELECT * FROM emp WHERE ename = p_name2';
17 end;
18
19 function set_operator_union(p_name1 varchar2, p_name2 varchar2) return clob sql_macro (table) is
20 begin
21 return sqm_set_op_generic(p_name1,p_name2,t_operator('UNION') );
22 end;
23
24 function set_operator_union_all(p_name1 varchar2, p_name2 varchar2) return clob sql_macro (table) is
25 begin
26 return sqm_set_op_generic(p_name1,p_name2,t_operator('UNION ALL') );
27 end;
28
29 end;
30 /
Package Body SQM_SET_OPS compiled
Listing 7: Hiding generic implementations in a package body
Looking at the SQM expansion pieces in the CBO trace was quite useful for me to understand the nature of some limitations we have using SQL macros. In the next post I will again look at it and try to understand how it might be related to the limitations when using SQL macros and the WITH clause.