I was playing with SQL macro and noticed SQL macro string parameters are passed as NULLs while all other parameter datatypes work fine:
CREATE OR REPLACE
FUNCTION TEST_NUMBER_MACRO(
P_VAL NUMBER
)
RETURN VARCHAR2 SQL_MACRO
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('P_VAL = "' || P_VAL || '"');
DBMS_OUTPUT.PUT_LINE('STMT = SELECT ''' || P_VAL || ''' VAL FROM DUAL');
RETURN 'SELECT ''' || P_VAL || ''' VAL FROM DUAL';
END;
/
CREATE OR REPLACE
FUNCTION TEST_VARCHAR2_MACRO(
P_VAL VARCHAR2
)
RETURN VARCHAR2 SQL_MACRO
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('P_VAL = "' || P_VAL || '"');
DBMS_OUTPUT.PUT_LINE('STMT = SELECT ''' || P_VAL || ''' VAL FROM DUAL');
RETURN 'SELECT ''' || P_VAL || ''' VAL FROM DUAL';
END;
/
Now:
SQL> SET SERVEROUTPUT ON
SQL> SELECT * FROM TEST_NUMBER_MACRO(777)
2 /
VAL
---
777
P_VAL = "777"
STMT = SELECT '777' VAL FROM DUAL
SQL> SELECT * FROM TEST_VARCHAR2_MACRO('XXX')
2 /
V
-
P_VAL = ""
STMT = SELECT '' VAL FROM DUAL
SQL>
Sounds like a bug.
SY.