Oracle SQL Macros
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Purpose SQL Macros are essentially a just a variant of a user-defined PL/SQL function that encapsulates a RETURN statement allowing the function to avoid the cost penalty of context switching.

SQL macros are valid in the SELECT and FROM clauses
Documented Yes
Exceptions
Error Code Reason
ORA-64625 bind variables not allowed in the string returned from SQL macro
ORA-64626 invalid SQL text returned from SQL macro: string
ORA-64627 cycle detected in SQL macro expansion
ORA-64628 scalar SQL macro cannot appear in FROM clause of a SQL statement
ORA-64629 table SQL macro can only appear in FROM clause of a SQL statement
ORA-64630 unsupported use of SQL macro: string
ORA-64632 SQL macro is not supported with <feature_name>
ORA-64633 SQL macro cannot be used remotely
ORA-64634 SQL macro length(string) was too big
First Available 20c
Macro Types SCALAR: The procedure is a SQL macro for a scalar expression
TABLE: The procedure is a SQL macro for a table expression
Security Model Owned by SYS with EXECUTE granted to ???
Sources Database Error Messages
Database Globalization Support Guide
Database Licensing Information
Database PL-SQL Language Reference
Database Reference
Learning Database New Features
SQL Language Reference
Syntax CREATE OR REPLACE FUNCTION [schema_name.]<function_name> RETURN <data_type> SQL_MACRO(<SCALAR | TABLE>) IS
BEGIN
  q'! <return_statement> !';
END;
/
 
SQL Macro Demo 1 (new 20c)
Create demo table CREATE TABLE test AS
SELECT object_name, created, last_ddl_time
FROM all_objects
WHERE created <> last_ddl_time
AND object_type = 'FUNCTION';

SELECT t.object_name, t.last_ddl_time, t.created
FROM test t
WHERE rownum = 1;
Test SQL with complex function call SELECT t.object_name, GREATEST((SYSDATE-t.last_ddl_time),(t.last_ddl_time - t.created)) AS DATEDIFF
FROM test t;
Create SQL Macro CREATE OR REPLACE FUNCTION elapsed_time RETURN VARCHAR2 SQL_MACRO(SCALAR) IS
BEGIN
  RETURN q'! GREATEST((SYSDATE-t.last_ddl_time),(t.last_ddl_time - t.created)) !';
END elapsed_time;
/
If PL/SQL warnings are enabled they will incorrectly indicate that the AUTHID clause is missing. Ignore the warning and do not create an AUTHID clause. SQL> sho err
Errors for FUNCTION JOB_DURATION:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1 PLW-05018: unit ELAPSED_TIME omitted optional AUTHID clause;
default value DEFINER used
SQL Macro use demo SELECT t.*, elapsed_time AS DATEDIFF
FROM test t
WHERE elapsed_time < 30;

OBJECT_NAME   CREATED              LAST_DDL_TIME        DATEDIFF
------------- -------------------- -------------------- ----------
TEST_PROC     03-JAN-2021 19:20:42 03-JAN-2021 19:37:14 .213298611
 
SQL Macro Demo 2 (new 20c)
Create demo table and data CREATE TABLE contacts (
first_name VARCHAR2(30),
last_name  VARCHAR2(30));

ALTER TABLE contacts
ADD CONSTRAINT pk_contacts
PRIMARY KEY (first_name, last_name);

INSERT INTO contacts (first_name, last_name) VALUES ('Tom', 'Kyte');
INSERT INTO contacts (first_name, last_name) VALUES ('Jonathan', 'Lewis');
INSERT INTO contacts (first_name, last_name) VALUES ('Dan', 'Morgan');
INSERT INTO contacts (first_name, last_name) VALUES ('Mogens', 'Norgaard');
INSERT INTO contacts (first_name, last_name) VALUES ('Caleb', 'Small');
INSERT INTO contacts (first_name, last_name) VALUES ('Ed', 'Whalen');
COMMIT;

SELECT * FROM contacts;

FIRST_NAME         LAST_NAME
------------------ ------------------
Tom                Kyte
Jonathan           Lewis
Dan                Morgan
Mogens             Norgaard
Caleb              Small
Ed                 Whalen
Test SQL with complex function call SELECT TRIM(UPPER(last_name) || ', ' || LOWER(first_name)) AS cname
FROM contacts;

CNAME
-----------------
KYTE, tom
LEWIS, jonathan
MORGAN, dan
NORGAARD, mogens
SMALL, caleb
WHALEN, ed
Create SQL Macro CREATE OR REPLACE FUNCTION name_convert(first_name VARCHAR2, last_name VARCHAR2) RETURN VARCHAR2 SQL_MACRO(SCALAR) IS
BEGIN
  RETURN q'{ TRIM(UPPER(last_name) || ', ' || LOWER(first_name)) }';
END name_convert;
/
SQL Macro use demo SELECT c.first_name, c.last_name, name_convert(c.first_name, c.last_name) AS CName
FROM contacts c;

FIRST_NAME  LAST_NAME  CNAME
----------- ---------- ------------------------
Tom         Kyte       KYTE, tom
Jonathan    Lewis      LEWIS, jonathan
Dan         Morgan     MORGAN, dan
Mogens      Norgaard   NORGAARD, mogens
Caleb       Small      SMALL, caleb
Ed          Whalen     WHALEN, ed
SQL Macro Reverse Engineered SELECT dbms_metadata.get_ddl('FUNCTION', 'NAME_CONVERT') FROM dual;

DBMS_METADATA.GET_DDL('FUNCTION','NAME_CONVERT')
--------------------------------------------------------------------------------
CREATE OR REPLACE NONEDITIONABLE FUNCTION "SYS"."NAME_CONVERT" (first_name VARCHAR2, last_name VARCHAR2) RETURN VARCHAR2 SQL_MACRO(SCALAR) IS
BEGIN
RETURN q'{ TRIM(INITCAP(first_name) || ' ' || INITCAP(last_name)) }';
END name_convert;
SQL Macro Explain Plan Output

No difference with, or without, the SQL Macro
EXPLAIN PLAN FOR
SELECT TRIM(UPPER(last_name) || ', ' || LOWER(first_name)) AS CName
FROM contacts;

SELECT * FROM TABLE(dbms_xplan.display);

------------------------------------------------------------------------------
| Id | Operation        | Name        | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|  0 | SELECT STATEMENT |             |    6 |   204 |       1 (0)| 00:00:01 |
|  1 |  INDEX FULL SCAN | PK_CONTACTS |    6 |   204 |       1 (0)| 00:00:01 |
------------------------------------------------------------------------------

EXPLAIN PLAN FOR
SELECT c.first_name, c.last_name, name_convert(c.first_name, c.last_name) AS CName
FROM contacts c;

SELECT * FROM TABLE(dbms_xplan.display);

------------------------------------------------------------------------------
| Id | Operation        | Name        | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|  0 | SELECT STATEMENT |             |    6 |   204 |       1 (0)| 00:00:01 |
|  1 |  INDEX FULL SCAN | PK_CONTACTS |    6 |   204 |       1 (0)| 00:00:01 |
------------------------------------------------------------------------------
SQL Macro SQL Expansion DECLARE
 vClobIn  CLOB := 'SELECT c.first_name, c.last_name, name_convert(c.first_name, c.last_name) AS CName FROM contacts c';

 vClobOut CLOB;
BEGIN
  dbms_utility.expand_sql_text(vClobIn, vClobOut);
  dbms_output.put_line(vClobOut);
END;
/
SELECT "A1"."FIRST_NAME" "FIRST_NAME","A1"."LAST_NAME" "LAST_NAME","SYS"."NAME_CONVERT"("A1"."FIRST_NAME","A1"."LAST_NAME") "CNAME" FROM "SYS"."CONTACTS" "A1"

PL/SQL procedure successfully completed.
Tim Hall's Query col owner format a20
col object_name format a20
col procedure_name format a15
col sql_macro format a9

SELECT p.owner, o.object_type, p.sql_macro, p.object_name, p.procedure_name
FROM dba_procedures p JOIN dba_objects o ON p.object_id = o.object_id
WHERE p.sql_macro != 'NULL'
AND p.owner = USER
ORDER BY p.owner, o.object_type, p.sql_macro, p.object_name, p.procedure_name;

OWNER  OBJECT_TYPE      SQL_MACRO  OBJECT_NAME   PROCEDURE_NAME
------ ---------------- ---------- ------------- ---------------
SYS    FUNCTION         SCALAR     ELAPSED_TIME
SYS    FUNCTION         SCALAR     NAME_CONVERT
Performance Comparison

Multiple timings we consistent from the standpoint that SQL without the SQL Macro was always the fastest but not significantly and PL/SQL was always substantially slower.
CREATE OR REPLACE FUNCTION cntxSwitch RETURN dbms_id AUTHID DEFINER IS
 s dbms_id;
BEGIN
  SELECT MAX(TRIM(UPPER(last_name) || ', ' || LOWER(first_name)))
  INTO s
  FROM contacts;

  RETURN s;
END cntxSwitch;
/

set timing on

DECLARE
 i NUMBER;
 j NUMBER;
 k NUMBER;
 s dbms_id;
BEGIN
  i := dbms_utility.get_time;
 
  FOR n IN 1 .. 100000 LOOP
    SELECT MAX(TRIM(UPPER(last_name) || ', ' || LOWER(first_name)))
    INTO s
    FROM contacts;
  END LOOP;
  dbms_output.put_line('SQL:       ' || TO_CHAR(dbms_utility.get_time - i));

  j := dbms_utility.get_time;

  FOR n IN 1 .. 100000 LOOP 
    SELECT MAX(name_convert(c.first_name, c.last_name))
    INTO s
    FROM contacts c;
  END LOOP;
  dbms_output.put_line('SQL Macro: ' || TO_CHAR(dbms_utility.get_time - j));

  k := dbms_utility.get_time;
  FOR n IN 1 .. 100000 LOOP
    SELECT cntxSwitch
    INTO s
    FROM dual;
  END LOOP;
  dbms_output.put_line('PL/SQL:    ' || TO_CHAR(dbms_utility.get_time - k));
END;
/
SQL:       155
SQL Macro: 192
PL/SQL:    559


set timing off

Related Topics
Built-in Functions
Built-in Packages
Database Security
User Defined Functions
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx