Skip to Main Content
  • Questions
  • Variable usage in SQL_MACRO function causing ORA-00984 error

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Michael.

Asked: November 09, 2022 - 4:14 pm UTC

Last updated: November 15, 2022 - 7:29 am UTC

Version: Oracle Database 19c

Viewed 100+ times

You Asked

I recently had a need to see some table data at regular 10 minute intervals throughout the day. I thought I would be able to use the following query:

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 TIMES.TIME,T.* FROM TIMES
CROSS JOIN AS_OF(TBL,TIMES.TIME) T



Making use of a SQL_MACRO function called AS_OF that parameterizes the timestamp part of an AS OF TIMESTAMP flashback query:

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 the AS_OF function throws an error ORA-00984: column not allowed here. It was my understanding that p_time would be treated like a bind variable, but that doesn't appear to be the case here. Could you clarify what's going on with non-table parameters to SQL_MACRO functions behind the scenes, and help me understand why this error gets thrown?

with LiveSQL Test Case:

and Chris said...

This is a bug - I've raised it.

I don't know of a workaround here other than don't use macros.

In general, if you want to see the final SQL statement generated by macros you can pass the query to dbms_utility.expand_sql_text:

create or replace function f ( p int ) 
  return clob sql_macro as
begin
  return ' select p * 2 from dual ';
end f;
/

select * from f ( 2 );

declare
  l_clob clob;
begin
  dbms_utility.expand_sql_text (
    input_sql_text  => 
      q'!select * from f ( 2 )!',
    output_sql_text => l_clob  );
  dbms_output.put_line(l_clob);
end;
/

select
  "A1"."P*2" "P*2"
from
  (
    select
      "A2"."P*2" "P*2"
    from
      (
        select
          2 * 2 "P*2"
        from
          "SYS"."DUAL" "A3"
      ) "A2"
  ) "A1";


But in this case there's a parse error in the statement, so this doesn't help:

declare
  l_clob clob;
begin
  dbms_utility.expand_sql_text (
    input_sql_text  => 
      q'!SELECT * FROM AS_OF(TESTTAB,SYSTIMESTAMP - INTERVAL '2' SECOND)!',
    output_sql_text => l_clob  );
  dbms_output.put_line(l_clob);
end;
/

ORA-24256: EXPAND_SQL_TEXT failed with ORA-00984: column not allowed here

Rating

  (5 ratings)

Comments

Obviously

A reader, November 14, 2022 - 6:00 am UTC

This "bug" is not in SQL Macro.
select *
from (select systimestamp-interval'1'hour tx from dual)
cross apply (select * from dual as of timestamp (tx));

ORA-00984: column not allowed here


Docs:
Restrictions on Flashback Queries

These queries are subject to the following restrictions:
* You cannot specify a column expression or a subquery in the expression of the AS OF clause.
* ...

Re: "Obviously"

Stew Ashton, November 14, 2022 - 11:42 am UTC

The error message repeats what the documentation says, but it is misapplied here. In the macro, "p_time" is not a column or expression, it is a bind variable, and bind variables are allowed in AS OF clauses:
SQL> var v_scn number;
SQL> begin
  2    select current_scn into :v_scn from v$database;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> print v_scn

     V_SCN
----------
38995488874922

SQL> /
SQL> select count(*) from emp as of scn :v_scn
  2  /

  COUNT(*)
----------
        14
The parser doesn't realize that "p_time" is a bind variable. That is a bug.
Chris Saxon
November 14, 2022 - 1:29 pm UTC

Exactly - passing the AS OF time/scn to a macro should work.

To bind or not to bind

A reader, November 14, 2022 - 3:22 pm UTC

Is it possible for a variable to change value during execution?
with function smbi(n number) return varchar2 sql_macro as
begin
  return q'{select 'N='||n "Bind?" from dual}';
end;
select *
from (select 123 x from dual union all select 456 from dual) t
cross apply (select * from smbi(t.x))
/

         X Bind?                                     
---------- ------------------------------------------
       123 N=123                                     
       456 N=456                                     

Expand_sql_text shows the usage of the correlated column "A3"."X", where A3 is an alias of the source table T.
with function est(sql_text varchar2) return clob as
  res clob;
begin
  dbms_utility.expand_sql_text(sql_text, res);
  return res;
end;
select est(q'[with function smbi(n number) return varchar2 sql_macro as
begin
  return q'{select 'N='||n "Bind?" from dual}';
end;
select *
from (select 123 x from dual union all select 456 from dual) t
cross apply (select * from smbi(t.x))]') est from dual;
/

SELECT "A1"."X_0" "X","A1"."Bind?_1" "Bind?"
FROM
 (SELECT "A3"."X" "X_0","A2"."Bind?_0" "Bind?_1"
  FROM  
   ( (SELECT 123 "X" FROM "SYS"."DUAL" "A9") UNION ALL
     (SELECT 456 "456" FROM "SYS"."DUAL" "A8")) "A3",
   LATERAL( (SELECT "A4"."Bind?" "Bind?_0"
             FROM
              ( SELECT "A5"."Bind?" "Bind?"
                FROM
                 ( SELECT 'N='||TO_CHAR("A3"."X") "Bind?"
                   FROM "SYS"."DUAL" "A6") "A5"
              ) "A4"
          ) ) "A2"
 ) "A1"

If you pass the actual value as a bind/literal, it will still be wrapped into the subquery A5.
Connor McDonald
November 15, 2022 - 7:29 am UTC

I think the term "bind" here is perhaps the cause of consternation.

*Conceptually* the parameter to a sql macro can be likened to a bind, but its not officially a bind variable. You wont find the term "bind" on the SQL_MACRO docs:

https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/sql_macro-clause.html#GUID-292C3A17-2A4B-4EFB-AD38-68DF6380E5F7

So in that sense, its not a variable and thus there is no issue with it changing. In this case, you've ended up with something more resembling a scalar query.

But definitely an interesting example!

On "to bind or not to bind"

Stew Ashton, November 15, 2022 - 11:31 am UTC

I take issue with the reader's comment and Connor's reply.

True, a SQL macro parameter is not "officially" a bind variable. So what? NO PL/SQL identifier (variable or formal parameter) is a bind variable. However:

"A PL/SQL static SQL statement can have a PL/SQL identifier wherever its SQL counterpart can have a placeholder for a bind variable. The PL/SQL identifier must identify either a variable or a formal parameter."
https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/static-sql.html#GUID-A22B737E-68B3-47A5-8EB3-3EDC53D8571D

In a SQL macro, a scalar formal parameter should be included by name in the returned string. That name becomes a placeholder for a bind variable in the SQL counterpart.

In the reader's example, the parameter value is concatenated into the statement. Since that "value" winds up being a column in the referenced subquery, it can change with each row. There are no "variables" here.



Correction

Stew Ashton, November 15, 2022 - 11:48 am UTC

In my first comment on this thread, I wrote "The parser doesn't realize that "p_time" is a bind variable".

I suppose Connor was referring to this when he said macro parameters are not "officially" bind variables.

I should have said something like "the parser doesn't realize that "p_time" should become a bind variable placeholder in the SQL counterpart".

My apologies for "taking issue" with Connor when I was actually taking issue with myself !

Best regards,
Stew

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here