Hi all,
I've hit what looks like a bug when chaining three levels of SQL Table Macros (STMs). One of the
key benefits of layered STMs is the ability to debug each level independently by querying it
directly -- this repro is structured to show exactly that.
Parameter tokens passed from level 3 down to level 2 are not resolved when level 2 calls a helper
function with its own parameter during return string construction. Replacing the parameter tokens
with hardcoded literals at level 3 works fine. Levels 1 and 2 work correctly when called directly.
Tested on Oracle 23ai (23.26.x).
================================================================================
WHAT WORKS
================================================================================
-- Debug level 1 directly
SELECT * FROM stm_pkg.stm_70(20); -- works
-- Debug level 2 directly
SELECT * FROM stm_pkg.stm_80(20); -- works
-- Level 3 with hardcoded literal
SELECT * FROM stm_pkg.stm_90a(20); -- works
================================================================================
WHAT FAILS
================================================================================
-- Level 3 with parameter token pass-thru
SELECT * FROM stm_pkg.stm_90b(20); -- ORA-06553 / PLS-306
================================================================================
FULL REPRO -- run in SQL Developer (sqlplus / SQLcl)
================================================================================
SET LINESIZE 200
SET PAGESIZE 50
COLUMN id FORMAT 99999 HEADING 'ID'
COLUMN val FORMAT 99999 HEADING 'VAL'
------------------------------------------------------------
-- DDL
------------------------------------------------------------
DROP PACKAGE IF EXISTS stm_pkg;
DROP TABLE IF EXISTS stm_data;
CREATE TABLE stm_data (
id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
val NUMBER NOT NULL
);
------------------------------------------------------------
-- DML
------------------------------------------------------------
INSERT INTO stm_data (val)
SELECT ROWNUM * 10
FROM DUAL CONNECT BY ROWNUM <= 5;
COMMIT;
CREATE OR REPLACE PACKAGE stm_pkg AS
-- Helper: accepts and returns integer
FUNCTION get_threshold(p_val INTEGER) RETURN INTEGER;
-- Level 1: filters by val
-- Debug: SELECT * FROM stm_pkg.stm_70(20);
FUNCTION stm_70(p_val INTEGER) RETURN CLOB SQL_MACRO(TABLE);
-- Level 2: selects from stm_70, passes p_val into helper
-- Debug: SELECT * FROM stm_pkg.stm_80(20);
FUNCTION stm_80(p_val INTEGER) RETURN CLOB SQL_MACRO(TABLE);
-- Level 3a: hardcoded literal pass-thru -- works
-- Debug: SELECT * FROM stm_pkg.stm_90a(20);
FUNCTION stm_90a(p_val INTEGER) RETURN CLOB SQL_MACRO(TABLE);
-- Level 3b: parameter token pass-thru -- fails
-- Debug: SELECT * FROM stm_pkg.stm_90b(20);
FUNCTION stm_90b(p_val INTEGER) RETURN CLOB SQL_MACRO(TABLE);
END stm_pkg;
/
CREATE OR REPLACE PACKAGE BODY stm_pkg AS
FUNCTION get_threshold(p_val INTEGER) RETURN INTEGER IS
BEGIN
RETURN p_val;
END get_threshold;
FUNCTION stm_70(p_val INTEGER) RETURN CLOB SQL_MACRO(TABLE) IS
BEGIN
RETURN q'[SELECT * FROM stm_data WHERE val >= stm_70.p_val]';
END stm_70;
-- get_threshold() called with stm_80's own parameter during string build
FUNCTION stm_80(p_val INTEGER) RETURN CLOB SQL_MACRO(TABLE) IS
BEGIN
RETURN q'[SELECT * FROM stm_pkg.stm_70(]' || get_threshold(p_val) || ')';
END stm_80;
-- Level 3a: hardcoded literal -- works
FUNCTION stm_90a(p_val INTEGER) RETURN CLOB SQL_MACRO(TABLE) IS
BEGIN
RETURN q'[SELECT * FROM stm_pkg.stm_80(20)]';
END stm_90a;
-- Level 3b: parameter token -- fails
FUNCTION stm_90b(p_val INTEGER) RETURN CLOB SQL_MACRO(TABLE) IS
BEGIN
RETURN q'[SELECT * FROM stm_pkg.stm_80(stm_90b.p_val)]';
END stm_90b;
END stm_pkg;
/
------------------------------------------------------------
-- Step 1: helper standalone -- expected: 20
------------------------------------------------------------
SELECT stm_pkg.get_threshold(20) AS threshold FROM DUAL;
------------------------------------------------------------
-- Step 2: stm_70 direct (level 1) -- expected: rows where val >= 20
-- NB: queryable independently -- this is the debug benefit of layered STMs
------------------------------------------------------------
SELECT * FROM stm_pkg.stm_70(20);
------------------------------------------------------------
-- Step 3: stm_80 direct (level 2) -- expected: same rows
-- NB: queryable independently -- confirms level 2 correct in isolation
------------------------------------------------------------
SELECT * FROM stm_pkg.stm_80(20);
------------------------------------------------------------
-- Step 4: stm_90a (level 3, hardcoded literal) -- expected: works
------------------------------------------------------------
SELECT * FROM stm_pkg.stm_90a(20);
------------------------------------------------------------
-- Step 5: stm_90b (level 3, parameter token) -- expected: works
-- actual: ORA-06553 / PLS-306
------------------------------------------------------------
SELECT * FROM stm_pkg.stm_90b(20);
================================================================================
OBSERVED OUTPUT
================================================================================
Steps 1-4: all return correct results.
Step 5:
SQL Error: ORA-06553: PLS-306: wrong number or types of arguments
in call to 'STM_70'
================================================================================
WORKAROUND
================================================================================
None found. Hardcoding literals at level 3 (stm_90a) works but defeats both
the purpose of parameterised layered STMs and the ability to debug each layer
independently with meaningful parameter values.
The real-world use case driving this is a three-level STM chain where level 2
applies a MATCH_RECOGNIZE clause built dynamically via a helper function.
Happy to post that extended repro if useful.
================================================================================
REFERENCES
================================================================================
Nesting behaviour of SQL macros discussed here (worth a read):
https://blog.sqlora.com/en/sql-macros-some-less-obvious-facts-part-1/
The above confirms that nesting in the macro body is supported and intended,
but does not document this specific failure mode.
Thanks in advance for any insight.