SQL table macros 2: “polymorphic” is not “dynamic”

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 1: a moving target?
• SQL table macros 2: “polymorphic” is not “dynamic”
• SQL table macros 3: parameter strangeness

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:

1. Our PL/SQL code sees a record whose values we can access.
2. 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 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 from DBMS_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)
  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 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(
  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.

Synthesis

A SELECT using a SQL table macro is a two-stage rocket:

1. At hard parse time, the macro executes and returns a string containing a SELECT statement,
   which is then parsed.
2. 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 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 TABLE_T and COLUMNS_T parameters are identifiers:

• they are part of the SQL statement that SELECTs FROM the macro and cannot be replaced by bind variables, even with CURSOR_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 generated SELECT statement as bind variables.
  (The generated statement can be seen using DBMS_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”.