Search code examples
macrosoracle-sqldeveloper

How to use proper syntax when creating SQL Macro?

Using Oracle SQL Developer, I am trying to make this web (link) example working:

CREATE OR REPLACE FUNCTION concat_self(str VARCHAR2, cnt PLS_INTEGER)
                    RETURN VARCHAR2 SQL_MACRO(SCALAR)
IS BEGIN            RETURN 'rpad(str, cnt * length(str), str)';
END;
/

But I get those errors I do not understand:

Function CONCAT_SELF compiled

LINE/COL  ERROR
--------- -------------------------------------------------------------
2/37      PLS-00103: Encountered the symbol "SQL_MACRO" when expecting one of the following:     . @ % ; is authid as cluster order using external character    deterministic parallel_enable pipelined aggregate    result_cache accessible 
3/4       PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:     not null of nan infinite dangling a empty 
5/0       PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:     end not pragma final instantiable order overriding static    member constructor map 
Errors: check compiler log
Solution

  • Your code is perfectly 'valid' for any instance of Oracle where SQL_MACRO keyword is recognized by the PL/SQL Engine.

    enter image description here

    The errors start to make a little bit more sense once you realize that the database doesn't understand what you're asking for - it does not recognize that 'SQL_MACRO' is a valid component of the CREATE OR REPLACE FUNCTION PL/SQL library.

    Those errors kind of allow you to see how the database's PL/SQL and SQL parser are taking your request and breaking it down into things it knows how to work with.

    Everything after the first error is about the parser not being able to make it past the first problem it encountered.

    This feature was introduced in version 21c of the database, as explained in the 21c New Features Guide.

    You can create SQL Macros (SQM) to factor out common SQL expressions and statements into reusable, parameterized constructs that can be used in other SQL statements. SQL macros can either be scalar expressions, typically used in SELECT lists, WHERE, GROUP BY and HAVING clauses, to encapsulate calculations and business logic or can be table expressions, typically used in a FROM clause.

    SQL macros increase developer productivity, simplify collaborative development, and improve code quality.