A polymorphic SQL table macro can produce rows with different “shapes”, but the “shape” is established at parse time, not execution time. (By “shape” I mean the number of columns, their names and their characteristics such as data type.)
These macros work very differently from what we are used to. My goal here is to help understand how they work, not blow you away with an advanced use case. The examples are just illustrations, not meaningful solutions.
Current list of posts:
- 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
- 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
Starting simple: the TABLE_T parameter
Let’s call a Polymorphic (SQL) Table Macro a PTM for short. Here is a simple one:
SQL> create or replace function noop1(p_table in dbms_tf.table_t)<br> 2 return varchar2 sql_macro is<br> 3 begin<br> 4 dbms_output.put_line('table_schema_name: ' || p_table.table_schema_name);<br> 5 dbms_output.put_line('table_name: ' || p_table.table_name);<br> 6 return 'select * from p_table';<br> 7 end;<br> 8 /</p> <p>Function NOOP1 compiled<br>
The input parameter p_table
has type DBMS_TF.TABLE_T
. It is used in two different ways:
- Our PL/SQL code sees a record whose values we can access.
- The literal ‘p_table’ appears in the returned string where a table name would normally go, and the actual table name is substituted.
Think how strange that is: the code returns a literal, but part of the literal is really a placeholder for the value of the input parameter! The strangeness continues when we call the macro:
<br> SQL> select * from noop1(dual);</p> <p>D<br> -<br> X</p> <p>table_schema_name: "SYS"<br> table_name: "DUAL"</p> <p>SQL> select * from noop1("DUAL");</p> <p>D<br> -<br> X</p> <p>table_schema_name: "SYS"<br> table_name: "DUAL"</p> <p>SQL> with x as (select * from dual)<br> 2 select * from noop1(x);</p> <p>D<br> -<br> X</p> <p>table_schema_name: ""<br> table_name: ""<br>
- Notice that the input parameter is not enclosed in single quotes: it is not a literal, but an identifier that is treated just the same as any other table name (or named subquery) that might be found in a
SELECT
statement. - When the input parameter refers to a named subquery, the schema and table name are left empty. The content of the named subquery is plugged into the
SELECT
statement as an inline view. - If we execute any of these
SELECT
statements twice in a row, the output fromDBMS_OUTPUT.PUT_LINE
will only appear once. This illustrates that the macro only executes when the statement is parsed.
Another simple example
If the TABLE_T
input parameter references a real table or view (not a named subquery), we can plug the values from the input record into the return string.
create or replace function noop2(p_table in dbms_tf.table_t)<br> return varchar2 sql_macro is<br> begin<br> dbms_output.put_line('table_schema_name: ' || p_table.table_schema_name);<br> dbms_output.put_line('table_name: ' || p_table.table_name);<br> return<br> case<br> when p_table.table_name = '""' then<br> 'select * from p_table'<br> else<br> 'select * from '|| p_table.table_schema_name || '.' || p_table.table_name<br> end;<br> end;<br> /
Notice that in one case we write the parameter name and the macro uses it as a placeholder, but in the other case we concatenate input values into the SELECT
statement ourselves.
Another new parameter type: COLUMNS_T
PTMs also accept input parameters of type DBMS_TF.COLUMNS_T
. Let’s use this to decide which columns we want to see in the output.
create table t(a,b,c,d) as<br> select 1,2,3,4 from dual;</p> <p>create or replace function pick_columns(<br> p_table in dbms_tf.table_t,<br> p_columns in dbms_tf.columns_t<br> )<br> return varchar2 sql_macro is<br> begin<br> return 'select p_columns from p_table';<br> end;<br> /<br> select * from pick_columns(t, columns(a,b,c));</p> <p>ORA-00904: "P_COLUMNS": invalid identifier
Oops, it looks like automatic substitution doesn’t work. Let’s plug the values in ourselves.
create or replace function pick_columns(<br> p_table in dbms_tf.table_t,<br> p_columns in dbms_tf.columns_t<br> )<br> return varchar2 sql_macro is<br> l_columns varchar2(4000);<br> l_sql varchar2(4000);<br> begin<br> for i in 1..p_columns.count loop<br> l_columns := l_columns<br> || case when l_columns is not null then ',' end<br> || p_columns(i);<br> end loop;<br> l_sql := 'select ' || l_columns || ' from p_table';<br> dbms_output.put_line(l_sql);<br> return l_sql;<br> end;<br> /<br> select * from pick_columns(t, columns(a,b,c));</p> <p> A B C<br> ---------- ---------- ----------<br> 1 2 3</p> <p>select "A","B","C" from p_table
Lots of things to notice here:
- When calling the function, the
COLUMNS
pseudo-operator must be used. The list contains valid simple identifiers. - The input appears to our code as a nested table containing quoted identifiers.
- We have to plug the values into the
SELECT
statement ourselves. - When we print the statement ourselves, we see our own substitutions but the ‘p_table’ literal has not been replaced yet.
Other parameters
What happens if we use good old scalar parameters? This example takes as input a table, a column and a number, then returns the column value multiplied by the number.
create or replace function multiplier(<br> p_table in dbms_tf.table_t,<br> p_columns in dbms_tf.columns_t,<br> p_multiplier in number<br> )<br> return varchar2 sql_macro is<br> l_sql varchar2(4000);<br> begin<br> l_sql := 'select ' || p_columns(1) || ', '<br> || p_columns(1)<br> || ' * p_multiplier multiplied_value from p_table';<br> dbms_output.put_line('p_multiplier = <' || p_multiplier || '>');<br> dbms_output.put_line(l_sql);<br> return l_sql;<br> end;<br> /</p> <p>SQL> select * from multiplier(t, columns(a), 9);</p> <p> A MULTIPLIED_VALUE<br> ---------- ----------------<br> 1 9</p> <p>p_multiplier = <9><br> select "A", "A" * p_multiplier multiplied_value from p_table<br>
The number 9 is a literal, and as such is part of the statement text; it is also available to the function code. However, in the returned string we should place the parameter name, not the value, just like the table parameter. We can see why if we use a bind variable instead of a literal.
<br> SQL> var n number;<br> SQL> exec :n := 20;</p> <p>PL/SQL procedure successfully completed.</p> <p>SQL> select * from multiplier(t, columns(a), :n);</p> <p> A MULTIPLIED_VALUE<br> ---------- ----------------<br> 1 20</p> <p>p_multiplier = <><br> select "A", "A" * p_multiplier multiplied_value from p_table<br>
Notice that the value of the p_multiplier
parameter is NULL
. Values of “normal” parameters are not available to the macro when it executes during the parsing phase.
<br> SQL> exec :n := 99;</p> <p>PL/SQL procedure successfully completed.</p> <p>SQL> select * from multiplier(t, columns(a), :n);</p> <p> A MULTIPLIED_VALUE<br> ---------- ----------------<br> 1 99
Notice here that the DBMS_OUTPUT.PUT_LINE
output doesn’t appear. Thanks to the use of a bind variable, the statement is not reparsed.
Synthesis
A SELECT
using a SQL table macro is a two-stage rocket:
- At hard parse time, the macro executes and returns a string containing a
SELECT
statement,
which is then parsed. - At execution time, the
SELECT
statement is executed. The macro doesn’t execute again, its work is done.
In other words, SQL table macros return code (the SELECT
statement), not data, and they do their work at parse time, not execution time.
SQL table macros can have two new types of input parameters:
DBMS_TF.TABLE_T
: refers to an existing table, view or named subqueryDBMS_TF.COLUMNS_T
: refers to a list of identifiers that are valid column names, whether they exist or not.
These are in addition to “classic” parameters with SQL or PL/SQL data types.
The values of the TABLE_T
and COLUMNS_T
parameters are identifiers:
- they are part of the SQL statement that
SELECT
sFROM
the macro and cannot be replaced by bind variables, even withCURSOR_SHARING=FORCE
; - their values are known at parse time, when the macro executes;
The “classic” parameters act differently depending on what the caller passes:
- variables: values are
NULL
at parse time. They appear in the generatedSELECT
statement as bind variables.
(The generated statement can be seen usingDBMS_UTILITY.EXPAND_SQL_TEXT
.) - literals: values are known at parse time and can be integrated directly into the
SELECT
statement.
Careful,CURSOR_SHARING=FORCE
turns the literals into bind variables, so never assume that “classic” parameter values are known at parse time!
At execution time, the values of the “classic” variable parameters are plugged into the bind variables of the final SELECT
statement before execution.
Conclusion: “polymorphic” vs. “dynamic”
If you ask me, a “dynamic” table macro would be like a PL/SQL function that returns a REF CURSOR
: the cursor can have a different “shape” at every execution, even when the parameters are the same, so the result is as “dynamic” as we want it to be. A SQL table macro is different, since it only works at parse time. That is why the documentation calls it “polymorphic” and never “dynamic”.
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
Stew
Is 21c or some specific 19.x version ?
Jim
Hi Jim,
I talk about that in my previous post. The answer is complex:
– SQL macros were originally part of 20c, which never became official.
– They are now officially part of 21c. I test using a 21c database on the Oracle Cloud Free Tier.
– SQL *table* macros were backported to 19c. That is where it gets confusing.
– The 19c version on the Oracle Cloud Free Tier says it is 19.5, but it has full support for SQL table macros.
– The on-premises 19.x versions supposedly have SQL table macros starting with 19.6 ; however, the macros only work with scalar data types.
Bottom line, in the Cloud *table* macros work the same in 19c and 21c. On premises 19.6 through 19.10 have limited functionality.
Add to this confusion the lack of documentation in 19c…
See details in my previous post.
Best regards, Stew
I am wondering why since the following declaration ‘p_columns in dbms_tf.columns_t and for i in 1..p_columns.count loop…end loop’, to retrieve each of the column names from COLUMNS() clause, we refer to variables/params by ‘p_columns(i)’ reference, not by the actual fields of data types in dbms_tf, I mean why we can’t refer in the examples above using something like ‘ … p_columns(i).description.name …’? (so basically using data types columns_t -> column_t -> column_metadata_t). Probably I am missing something obvious, but would be grateful for any help.
Hi Marek,
I think it’s just a matter of personal preference.
Best regards, Stew