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:
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) 2 return varchar2 sql_macro is 3 begin 4 dbms_output.put_line('table_schema_name: ' || p_table.table_schema_name); 5 dbms_output.put_line('table_name: ' || p_table.table_name); 6 return 'select * from p_table'; 7 end; 8 / Function NOOP1 compiled
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:
SQL> select * from noop1(dual); D - X table_schema_name: "SYS" table_name: "DUAL" SQL> select * from noop1("DUAL"); D - X table_schema_name: "SYS" table_name: "DUAL" SQL> with x as (select * from dual) 2 select * from noop1(x); D - X table_schema_name: "" table_name: ""
- 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
- 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
SELECTstatement as an inline view.
- If we execute any of these
SELECTstatements twice in a row, the output from
DBMS_OUTPUT.PUT_LINEwill only appear once. This illustrates that the macro only executes when the statement is parsed.
Another simple example
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) return varchar2 sql_macro is begin dbms_output.put_line('table_schema_name: ' || p_table.table_schema_name); dbms_output.put_line('table_name: ' || p_table.table_name); return case when p_table.table_name = '""' then 'select * from p_table' else 'select * from '|| p_table.table_schema_name || '.' || p_table.table_name end; end; /
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 select 1,2,3,4 from dual; create or replace function pick_columns( p_table in dbms_tf.table_t, p_columns in dbms_tf.columns_t ) return varchar2 sql_macro is begin return 'select p_columns from p_table'; end; / select * from pick_columns(t, columns(a,b,c)); 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( p_table in dbms_tf.table_t, p_columns in dbms_tf.columns_t ) return varchar2 sql_macro is l_columns varchar2(4000); l_sql varchar2(4000); begin for i in 1..p_columns.count loop l_columns := l_columns || case when l_columns is not null then ',' end || p_columns(i); end loop; l_sql := 'select ' || l_columns || ' from p_table'; dbms_output.put_line(l_sql); return l_sql; end; / select * from pick_columns(t, columns(a,b,c)); A B C ---------- ---------- ---------- 1 2 3 select "A","B","C" from p_table
Lots of things to notice here:
- When calling the function, the
COLUMNSpseudo-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
- When we print the statement ourselves, we see our own substitutions but the ‘p_table’ literal has not been replaced yet.
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( p_table in dbms_tf.table_t, p_columns in dbms_tf.columns_t, p_multiplier in number ) return varchar2 sql_macro is l_sql varchar2(4000); begin l_sql := 'select ' || p_columns(1) || ', ' || p_columns(1) || ' * p_multiplier multiplied_value from p_table'; dbms_output.put_line('p_multiplier = <' || p_multiplier || '>'); dbms_output.put_line(l_sql); return l_sql; end; / SQL> select * from multiplier(t, columns(a), 9); A MULTIPLIED_VALUE ---------- ---------------- 1 9 p_multiplier = <9> select "A", "A" * p_multiplier multiplied_value from p_table
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.
SQL> var n number; SQL> exec :n := 20; PL/SQL procedure successfully completed. SQL> select * from multiplier(t, columns(a), :n); A MULTIPLIED_VALUE ---------- ---------------- 1 20 p_multiplier = <> select "A", "A" * p_multiplier multiplied_value from p_table
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.
SQL> exec :n := 99; PL/SQL procedure successfully completed. SQL> select * from multiplier(t, columns(a), :n); A MULTIPLIED_VALUE ---------- ---------------- 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.
SELECT using a SQL table macro is a two-stage rocket:
- At hard parse time, the macro executes and returns a string containing a
which is then parsed.
- At execution time, the
SELECTstatement 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 subquery
DBMS_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
COLUMNS_T parameters are identifiers:
- they are part of the SQL statement that
FROMthe macro and cannot be replaced by bind variables, even with
- 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
NULLat parse time. They appear in the generated
SELECTstatement as bind variables.
(The generated statement can be seen using
- literals: values are known at parse time and can be integrated directly into the
CURSOR_SHARING=FORCEturns 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”.