Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

19C: SQL Macro passed string parameters

Solomon YakobsonDec 30 2020

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.

Comments

Post Details

Added on Dec 30 2020
76 comments
6,214 views