SQL table macros 3: parameter strangeness

Posted on by

Like everything else in SQL macros, parameters are used differently from what we know or expect. Understanding how they work is key to understanding what macros do – and what they don’t.

[Update 2021-09-30: Chris Saxon reminded me that literal values can be inspected by SQL macros; the fact that VARCHAR2 literal values are nulled is an exception.]

Parse, then execute

I recently realized that Oracle never executes SQL SELECT statements! It parses them to generate execution plans, then executes the execution plans. The actual SQL statement text that was submitted has no bearing on the execution itself. This is just like classic third generation languages (COBOL, C, etc.) where a compiler generates an executable which is totally separate from the source code and which executes independently.

We have to accept that SQL macros operate at hard parse time, period. Once the execution plan is generated, how it got generated is irrelevant: it no longer matters whether a macro was called during the parse.

Table macro parameters

SQL table macros can have three types of formal parameters:

  • Table parameters of type DBMS_TF.TABLE_T
  • Column parameters of type DBMS_TF.COLUMNS_T
  • Classic parameters

To see how they work, here is a sample macro with all three types.

create or replace function select_table_column_equal(
  p_table in dbms_tf.table_t,
  p_column in dbms_tf.columns_t,
  p_string in varchar2
)
return varchar2 sql_macro is
  l_sql varchar2(4000);
begin
  l_sql := 
   'select t.*, ''' || p_string || ''' s from p_table t'
   || ' where ' || p_column(1) || ' = p_string';
  dbms_output.put_line(l_sql);
  return l_sql;
end select_table_column_equal;
/

select * from select_table_column_equal(emp, columns(ename),'KING');

When we submit this SELECT statement, the macro runs during the hard parse phase, so dbms_output.put_line shows us the text the macro passed on to the parser :

select t.*, '' s from p_table t where "ENAME" = p_string

and the output, as expected, is:

EMPNO ENAME JOB       MGR HIREDATE     SAL  COMM  DEPTNO INPUT
----- ----- --------- --- ---------- ----- ----- ------- -----
 7839 KING  PRESIDENT     1981-11-17  5000            10

There is so much going on here!

Let’s start by looking at the p_string parameter. This is a classic VARCHAR2 parameter whose value should be available at execution time in order to choose the rows to return. However, it makes no sense to use its value at parse time to change the macro’s output text, since the value can vary at each execution. In fact, we see that during the parse phase the value of the parameter is considered to be null. (It is true, as Chris Saxon has reminded me, that NUMBER parameters can be inspected by SQL macros, provided the caller passes a literal and provided that the CURSOR_SHARING initialization parameter is set to EXACT. I wish this were considered a bug and already fixed…)

Let me repeat: the value of a classic parameter cannot must not be used to influence the content of the macro’s output text! In most cases that value is not available, I can’t stick it into the output, and I can’t use it in a condition within the macro.

What I should do is use the name of the parameter within the output text. It is a placeholder that tells the parser where the actual parameter should be used during execution.

Now look at the COLUMNS_T parameter. When calling the macro, I have to use the COLUMNS pseudo-operator, which contains a list of one or more identifiers that could be used as column names. Since they are identifiers, they are part of the statement to be parsed and cannot be replaced by bind variables or expressions. Unlike the classic parameter p_string, the name “p_column” cannot be used for anything, but the values can. The list of identifiers that I pass is converted to a table of quoted identifiers; I plug the first entry from that table into the text that the macro outputs.

OK, so far we’ve seen that classic parameters can must only be used as placeholders in the output text: they tell the parser where to use the actual parameters during execution. The name is used at parse time; the value is used at execution time. On the other hand, with COLUMNS_T parameters the name is never used, but the value(s) are available for use within the macro.

Finally, let’s look at the TABLE_T parameter. When we call the macro, we pass an identifier (emp), not a literal. This identifier has to refer to an existing table or view, or to a named subquery that precedes the call to the macro. Since it is an identifier, it is part of the text to be parsed and it cannot be replaced by a bind variable or other expression. Inside the macro, this parameter is a complex structure that contains a lot of information; however, here I don’t use that information at all, I just put the name of the parameter in the output text. The parser will see the name and substitute the table, view or named subquery as an inline view.

More details on COLUMNS_T

Again, the formal parameter is of type DBMS_TF.COLUMNS_T:

create function f(p_columns in DBMS_TF.COLUMNS_T)

The actual parameter is the COLUMNS pseudo-operator containing a list of identifiers that must be valid column names, in other words simple SQL names:

select * from f(columns(n,vis,"Fancy Name"))

The PL/SQL code in the function sees this parameter as a nested table of quoted identifiers, exactly as if the input was run through DBMS_ASSERT.ENQUOTE_NAME.

If quoted, all characters are allowed except \0 and the double quote.

We can concatenate these values into our output text, or use the values to influence our logic.
For example, we can say what data types we want to include or exclude.

So, if you want a foolproof way to pass a literal value to a SQL macro, disguise it as a column name!

As time goes on, I will probably use (and abuse) this parameter to do things that have nothing to do with column names ;)

Posts on SQL table macros

5 thoughts on “SQL table macros 3: parameter strangeness”

  1. Hi Stew,

    First I’d like to thank you for this post series (and others) — your blog has been extremely helpful and informative to me over the years.

    I recently needed to look at data in a table as of a bunch of different times throughout the day.. I first tried:

    WITH TIMES(TIME) AS (
    SELECT TRUNC(SYSDATE) TIME FROM DUAL
    UNION ALL
    SELECT TIME+ INTERVAL ’10’ MINUTE FROM TIMES WHERE TIME < SYSDATE – INTERVAL '10' MINUTE
    )
    SELECT * FROM TIMES
    JOIN TBL AS OF TIMESTAMP(TIMES.TIME) T ON 1=1

    And got ORA-00984: column not allowed here.. fair enough. So I thought this might be a case for a SQL macro.

    CREATE FUNCTION AS_OF (p_tab in DBMS_TF.TABLE_T, p_time in DATE) RETURN VARCHAR2 SQL_MACRO IS
    BEGIN
    RETURN 'SELECT * FROM p_tab AS OF TIMESTAMP(p_time)';
    END AS_OF;
    /

    But this also gives ORA-00984. I tried various permutations of the above, such as changing AS OF TIMESTAMP to AS OF SCN TIMESTAMP_TO_SCN(p_time), but continue to get an ORA-00984.

    I tried concatenating into the query string:

    create FUNCTION AS_OF (p_tab in DBMS_TF.TABLE_T, p_time in DATE) RETURN VARCHAR2 SQL_MACRO IS
    BEGIN
    RETURN 'SELECT * FROM p_tab AS OF TIMESTAMP(TO_DATE(''' || TO_CHAR(p_time,'DD-MON-YYYY HH24:MI:SS') || ''',''DD-MON-YYYY HH24:MI:SS''))';
    END AS_OF;
    /

    After reading your excellent post here, I understand why this didn't work the way I expected… though it ran without error and returned current table results, which I don't understand.

    I noticed that when using a "classic" parameter in the column list in a very simple example:

    create or replace FUNCTION PTEST (p_time in DATE) RETURN VARCHAR2 SQL_MACRO IS
    BEGIN
    RETURN 'select p_time from dual';
    END;
    /

    .. the resultset returned shows "p_time" as the column name. I would have thought that these parameters here would act like bind variables, but seeing it show as the column name combined with the ORA-00984 errors I was getting above, leads me to thinking that the SQL_MACRO is instead somehow treating these parameters as though they are columns from some pseudotable.

    Is what I'm trying to do here possible with SQL macros? Or possible at all? Appreciate any insight you can provide.

    Reply

    • Hi Mike and thanks for the kind words,

      There is too much going on here to reply to everything in detail; however, I think you are mixing things up when you say “seeing it show as the column name combined with the ORA-00984 errors I was getting above, leads me to thinking that the SQL_MACRO is instead somehow treating these parameters as though they are columns from some pseudotable.” There is no combining here, there are two different things. The parameter shows as the column name because you did not alias “p_time” so the name was used as the column name and the value was used as a bind variable.

      To get what you want, why don’t you use VERSIONS BETWEEN?

      select versions_starttime vest, versions_endtime veen,
      versions_operation veop, tbl.*
      from tbl
      versions between timestamp systimestamp – (systimestamp – trunc(systimestamp)) and systimestamp

      Reply

      • Thank you for the quick reply — my first time seeing VERSIONS BETWEEN and it does indeed get me what I needed.

        But I’m still wondering why the below throws an ORA-00984:

        CREATE FUNCTION AS_OF (p_tab in DBMS_TF.TABLE_T, p_time in DATE) RETURN VARCHAR2 SQL_MACRO IS
        BEGIN
        RETURN ‘SELECT * FROM p_tab AS OF TIMESTAMP(p_time)’;
        END AS_OF;
        /

        It doesn’t seem to be treating p_time as a bind variable in this case.

      • Yes, I get the same error in 21c and 19c, with TIMESTAMP or SCN. I don’t understand it either. Can you raise an SR, or ask on asktom.oracle.com?

Leave a comment