{"id":1885,"date":"2023-12-08T01:38:48","date_gmt":"2023-12-07T23:38:48","guid":{"rendered":"https:\/\/blog.sqlora.com\/en\/?p=1885"},"modified":"2023-12-08T01:38:51","modified_gmt":"2023-12-07T23:38:51","slug":"how-to-get-the-final-sql-after-macro-expansion","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/en\/how-to-get-the-final-sql-after-macro-expansion\/","title":{"rendered":"How to get the final SQL after macro expansion"},"content":{"rendered":"\n

In my very first post<\/a> about SQL macros, I mentioned that for table macros, there is a simple way to see the SQL statement after macro expansion using dbms_utility.expand_sql_text<\/strong><\/code>. However, for scalar SQL macros, there is no such straightforward method. We can activate a CBO trace (also known as event 10053) and find the final statement in the trace file. This approach works for both scalar and table SQL macros. In this post, we will explore how to do this, and we will use… a SQL macro for that! Well, at least we will give it a try…<\/p>\n\n\n\n\n\n\n\n

If you are a DBA, generating and accessing a CBO trace may not be an issue at all for you. If you are a developer then it may be a bit trickier in terms of the workflow and the required privileges. It would probably make sense to develop this process as a privileged user and encapsulate it in a definer rights PL\/SQL unit and then grant the developers the execute privilege on it. <\/p>\n\n\n\n

But do we have to use SQL macro for it? – Definitely not!<\/strong> We could develop a procedure or function returning CLOB or a pipelined function returning a content of a trace file row by row. But because I’m just trying to get to know the SQL macros more deeply – what limitations, edge cases, do’s and don’ts there are – I’m reaching for every opportunity to use them. That’s my reason for trying to solve the task as a SQL macro. And indeed, it paid off immediately. Because it was just the use case that made me realize that the behavior of the macros with regard to definer\/invoker rights does not correspond to the documentation, as described in the previous post<\/a>.<\/p>\n\n\n\n

The Approach<\/h2>\n\n\n\n

How do I expect the solution to work? We’ll develop a table SQL macro, which we can select from, passing the original statement with SQL macro(s) as parameter and getting the final query back. <\/p>\n\n\n\n

We will use the following SQL macro for the test (let’s name it test macro for clarity): <\/p>\n\n\n

\ncreate or replace function duration_years \n                    (p_date_from in date\n                   , p_date_to in date) \nreturn varchar2 sql_macro(scalar) as\nbegin\n   return q'[ floor(months_between(p_date_to, p_date_from)\/12) ]';\nend;\n\/\n<\/pre><\/div>\n\n\n
Listing 1<\/strong><\/em><\/p>\n\n\n\n
And here is the SQL statement using this macro that we want to inspect more closely:<\/p>\n\n\n
\nselect ename\n,      duration_years(hiredate, sysdate) as years\n,      duration_years(date '2000-01-01', sysdate) as years_y2k\nfrom   emp e;\n<\/pre><\/div>\n\n\n
Listing 2<\/em><\/strong><\/p>\n\n\n\n
<\/p>\n\n\n\n
Note 1: How to pass a query as a parameter?<\/h4>\n\n\n\n
It is not clearly documented, but the values for the text parameters are just not visible (nulled) inside the macro body. If we want to pass the query as a parameter, we can do this by wrapping it in a collection, as Stew Ashton proposes<\/a>. For example, using a pre-defined Oracle type sys.odcivarchar2list<\/strong>.<\/mark> If you want to learn more about SQL macros, I would highly recommend reading his blog (there are currently 13 posts about SQL macros). And have a look at his very useful package SQM_UTIL<\/strong>.<\/a> <\/p>\n\n\n\n
\n
SQL table macros 9: just pivot!<\/a><\/blockquote>