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.<\/p>\n\n\n\n\n\n\n\n
In the previous posts about finding the final SQL<\/a> and other information about SQM macro expansion in the CBO trace<\/a> 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<\/a>.<\/p>\n\n\n Listing 1: Function to show the content from CBO trace file about SQM expansion<\/em><\/strong><\/p>\n\n\n\n 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.<\/p>\n\n\n Listing 2: Table SQL macro and its expansion<\/em><\/strong><\/p>\n\n\n\n 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.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n 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.<\/p>\n\n\n Listing 3: Using a parameter otherwise than for a scalar expression fails<\/em><\/strong><\/p>\n\n\n\n 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.<\/p>\n\n\n Listing 4: The template SQL will fail if we just reference the parameter name.<\/em><\/strong><\/p>\n\n\n\ncreate or replace function sqm_trace_info (P_query in varchar2) \n return sys.odcivarchar2list pipelined is\n v_result varchar2(4000);\n v_tracefile varchar2(100) := 'SQM_'||to_char(sysdate, 'HH24MISS');\n v_dump_trace_cmd varchar2(500);\n v_sql_id varchar2(20);\n v_cur sys_refcursor;\n v_child number;\n v_trcline varchar2(32000);\nbegin\n -- get sql_id for the query\n v_sql_id := dbms_sql_translator.sql_id(p_query);\n -- parse query \n open v_cur for p_query;\n select max(child_number) into v_child from sys.v_$sql where sql_id = v_sql_id;\n close v_cur;\n \n if v_child is not null then \n v_dump_trace_cmd := \n replace(\n replace(\n replace( q'[\n begin \n DBMS_SQLDIAG.DUMP_TRACE( p_sql_id=>'#sql_id#'\n , p_child_number=> '#child#'\n , p_component=>'Compiler'\n , p_file_id=>'#v_trace_identifier#');\n end;]','#sql_id#',v_sql_id)\n , '#v_trace_identifier#', v_tracefile)\n , '#child#', v_child) ;\n \n -- create a trace file \n execute immediate v_dump_trace_cmd;\n\n -- return the query to find the relevant line \n v_result := q'[ select x.trcline from v$diag_trace_file_contents \n match_recognize (\n partition by trace_filename\n order by line_number\n measures payload as trcline\n all rows per match\n pattern (a | b nc*|c |f n)\n define a as (payload like 'qksptfSQM_GetTxt(): Anonymous Block%')\n , b as (payload like 'qksptfSQM_GetTxt(): Macro Text%')\n , nc as (payload not like 'qksptfSQM_Template(): Template Text%')\n , c as (payload like 'qksptfSQM_Template(): Template Text%') \n , f as (payload like 'Final query after%')\n ) x \n where trace_filename like '%#TRACEFILENAME#%' \n ]';\n open v_cur for replace (v_result,'#TRACEFILENAME#', v_tracefile);\n loop\n fetch v_cur into v_trcline;\n exit when v_cur%notfound;\n pipe row(v_trcline);\n end loop;\n else\n pipe row (replace(q'[The SQL_ID #sql_id# was not found!]', '#sql_id#', v_sql_id )); \n end if;\nend;\n\/\n<\/pre><\/div>\n\n\n
\nSQL> create or replace function sqm_set_op(p_name1 varchar2, p_name2 varchar2) \nreturn varchar2 sql_macro (table) \nis\nbegin\n return q'[SELECT * FROM emp WHERE ename = p_name1 \n UNION \n SELECT * FROM emp WHERE ename = p_name2]';\nend;\n\/\nFunction created.\n\nSQL> select * from sqm_trace_info(q'[select * from sqm_set_op('SCOTT','KING')]')\n\nCOLUMN_VALUE \n--------------------------------------------------------------------------------\nqksptfSQM_GetTxt(): Anonymous Block\n===================================\n \ndeclare \n \nbegin \n:macro_text := "SQM_SET_OP"(:3 ,:4 ); \nend; \n \nqksptfSQM_GetTxt(): Macro Text\n==============================\nSELECT * FROM emp WHERE ename = p_name1 \nUNION \nSELECT * FROM emp WHERE ename = p_name2\n \nqksptfSQM_Template(): Template Text\n===================================\n\nselect "SYS__$".* \nfrom (select NULL "P_NAME1",NULL "P_NAME2" from SYS.DUAL) "SQM_SET_OP",\n lateral(SELECT * FROM emp WHERE ename = p_name1 \n UNION \n SELECT * FROM emp WHERE ename = p_name2) "SYS__$"\n\nFinal query after transformations:******* UNPARSED QUERY IS *******\n\nSELECT "SYS__$"."EMPNO" "EMPNO",\n "SYS__$"."ENAME" "ENAME",\n "SYS__$"."JOB" "JOB",\n "SYS__$"."MGR" "MGR",\n "SYS__$"."HIREDATE" "HIREDATE",\n "SYS__$"."SAL" "SAL",\n "SYS__$"."COMM" "COMM",\n "SYS__$"."DEPTNO" "DEPTNO"\n FROM ( (SELECT "EMP"."EMPNO" "EMPNO",\n "EMP"."ENAME" "ENAME",\n "EMP"."JOB" "JOB",\n "EMP"."MGR" "MGR",\n "EMP"."HIREDATE" "HIREDATE",\n "EMP"."SAL" "SAL",\n "EMP"."COMM" "COMM",\n "EMP"."DEPTNO" "DEPTNO"\n FROM "SCOTT"."EMP" "EMP"\n WHERE "EMP"."ENAME" = 'SCOTT')\n UNION\n (SELECT "EMP"."EMPNO" "EMPNO",\n "EMP"."ENAME" "ENAME",\n "EMP"."JOB" "JOB",\n "EMP"."MGR" "MGR",\n "EMP"."HIREDATE" "HIREDATE",\n "EMP"."SAL" "SAL",\n "EMP"."COMM" "COMM",\n "EMP"."DEPTNO" "DEPTNO"\n FROM "SCOTT"."EMP" "EMP"\n WHERE "EMP"."ENAME" = 'KING')) "SYS__$";\n \n \n<\/pre><\/div>\n\n\n\nSQL> create or replace function sqm_set_op_generic(p_name1 varchar2\n , p_name2 varchar2\n , p_operator varchar2) \nreturn varchar2 sql_macro (table) \nis\nbegin\n return q'[SELECT * FROM emp WHERE ename = p_name1 \n p_operator \n SELECT * FROM emp WHERE ename = p_name2]';\nend;\nFunction created.\nSQL> select * from sqm_set_op_generic('SCOTT','KING', 'UNION')\n>> select * from sqm_set_op_generic('SCOTT','KING', 'UNION')\n *\nError at line 15\nORA-64626: invalid SQL text returned from SQL macro: \nORA-00907: missing right parenthesis\n<\/pre><\/div>\n\n\n\n>> select "SYS__$".* \nfrom (select NULL "P_NAME1",NULL "P_NAME2", NULL "P_OPERATOR" from SYS.DUAL) "SQM_SET_OP2",\n lateral(SELECT * FROM emp WHERE ename = p_name1 p_operator SELECT * FROM emp WHERE ename = p_name2) "SYS__$"\n *\nError at line 3\nORA-00907: missing right parenthesis\n<\/pre><\/div>\n\n\n