[SOLVED] Compilation error for SQL_MACRO in oracle 19C

Issue

create or replace function f_get_ids(p_user in nvarchar2)
return nvarchar2 sql_macro(table)
as
begin
  return q'[select id from table1 where user=p_user]';
end;

while executing the above code in oracle 19.0.0.0.0 version getting the below error.

Error(1,27): PLS-00103: Encountered the symbol "(" when expecting one of the following: ; is default authid as cluster order using external deterministic parallel_enable pipelined aggregate result_cache accessible rewrite

Solution

Simple fix once you get to the right patch level on 19c.

Because there is only ONE type of macro in 19c, you don’t need to quantify which type, eg

SQL> create or replace function f_get_ids(p_user in nvarchar2)
  2  return nvarchar2 sql_macro(table)
  3  as
  4  begin
  5    return q'[select id from table1 where user=p_user]';
  6  end;
  7  /

Warning: Function created with compilation errors.

SQL>
SQL>
SQL> create or replace function f_get_ids(p_user in nvarchar2)
  2  return nvarchar2 sql_macro     
  3  as
  4  begin
  5    return q'[select id from table1 where user=p_user]';
  6  end;
  7  /

Function created.

Same as when you asked here as well 🙂

https://asktom.oracle.com/pls/apex/asktom.search?tag=error-while-executing-sql-macro-in-19c

Answered By – Connor McDonald

Answer Checked By – Katrina (BugsFixing Volunteer)