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 variants 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 of a SQL statement.
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;
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
