19C: SQL Macro passed string parameters

Solomon Yakobson

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.

Paulzip

I don't think you're using macro parameters correctly...

CREATE OR REPLACE
 FUNCTION TEST_VARCHAR2_MACRO(
                P_VAL VARCHAR2
               )
  RETURN VARCHAR2 SQL_MACRO
  IS
  BEGIN
    RETURN 'SELECT TEST_VARCHAR2_MACRO.P_VAL as VAL FROM DUAL';
  END;
/

SELECT * FROM TEST_VARCHAR2_MACRO('XXX')
/

VAL
-------
XXX

Solomon Yakobson

Sorry, last line was there just to return a statement. Assume I want to pass parameter to SQL macro, do some processing and generate SELECT based on processing results:

CREATE OR REPLACE
  FUNCTION F1(
              P_VAL VARCHAR2
             )
    RETURN VARCHAR2
    IS
    BEGIN
        RETURN P_VAL || '123';
END;
/
CREATE OR REPLACE
  FUNCTION TEST_VARCHAR2_MACRO(
                               P_VAL VARCHAR2
                              )
    RETURN VARCHAR2 SQL_MACRO
    IS
        V_ADJUSTED_VAL VARCHAR2(10);
    BEGIN
        V_ADJUSTED_VAL := F1(TEST_VARCHAR2_MACRO.P_VAL);
        DBMS_OUTPUT.PUT_LINE('ADJUSTED VAL = "' || V_ADJUSTED_VAL || '"');
        RETURN 'SELECT ''' || V_ADJUSTED_VAL || ''' VAL FROM DUAL';
END;
/
SET SERVEROUTPUT ON
SELECT * FROM TEST_VARCHAR2_MACRO('ZZZ')
/

VAL
---
123

ADJUSTED VAL = "123"
SQL>

As you can see 'ZZZ' I passed to macro turned into NULL.

SY.

Solomon Yakobson

Same logic with SQL macro NUMBER parameter:

CREATE OR REPLACE
  FUNCTION TEST_NUMBER_MACRO(
                             P_VAL NUMBER
                            )
    RETURN VARCHAR2 SQL_MACRO
    IS
        V_ADJUSTED_VAL VARCHAR2(10);
    BEGIN
        V_ADJUSTED_VAL := F1(TEST_NUMBER_MACRO.P_VAL);
        DBMS_OUTPUT.PUT_LINE('ADJUSTED VAL = "' || V_ADJUSTED_VAL || '"');
        RETURN 'SELECT ''' || V_ADJUSTED_VAL || ''' VAL FROM DUAL';
END;
/
SET SERVEROUTPUT ON
SELECT * FROM TEST_NUMBER_MACRO(999)
/

VAL
------
999123

ADJUSTED VAL = "999123"
SQL>

SY.

Paulzip

No, you're still misusing SQL_Macro parameters because you're still thinking in terms of normal function parameters, not in terms of Macros.

1. SQL Macro parameters are referenced inside of the quoted string, they are a special parameter case.
2. As SQL Macro functions are executed once at parse time, but can be reused multiple times at execution time they shouldn't change. So constructing them in the manner you are doing goes against their design paradigm.
3. This seems verified by the fact all SQL_MACRO(TABLE) and SQL_MACRO(SCALAR) examples from Oracle I have seen, are string literals with the parameters embedded in the form I showed early.

I think polymorphic views allow some degree of what you are doing (different columns dependent on table name), but in those cases I think you have to pass the table name as DBMS_TF.TABLE_T. The parser knows to treat it in a special way.

CREATE OR REPLACE FUNCTION print_table(t DBMS_TF.TABLE_T ) RETURN VARCHAR2 SQL_MACRO AS
 v_cols clob ;
 v_unpivot clob ;
 v_str  varchar2(200);
 v_delimiter varchar2(9):= ',';
 v_name dbms_id;
 v_sql clob;
BEGIN
 FOR I IN 1..t.column.count LOOP
  v_name := t.column(i).description.name;
  IF t.column(i).description.type = dbms_tf.type_varchar2 THEN 
   v_str := v_name;
  ELSIF t.column(i).description.type = dbms_tf.type_number THEN
   v_str := 'to_char('||v_name||') as '||v_name;
  ELSIF t.column(i).description.type = dbms_tf.type_date THEN
   v_str := 'to_char('||v_name||',''YYYY-MM-DD HH24:MI:SS'') as '||v_name;
  END IF;
  v_cols := v_cols || v_delimiter || v_str;
  v_unpivot := v_unpivot || v_delimiter || v_name;
 END LOOP;
 v_cols := LTRIM(v_cols,',');
 v_unpivot := LTRIM(v_unpivot,',');
 v_sql := 'SELECT col_name, nvl(col_value,''(NULL)'') as col_value '||
     'FROM  (SELECT '|| v_cols ||' from t )'||
     ' UNPIVOT include nulls (col_value for col_name 
                in ('||v_unpivot||') )';
 RETURN v_sql;     
END;
/

SQL> select * from print_table(scott.dept);
 
COL_NA COL_VALUE                               
------ ----------------------------------------
DEPTNO 10                                      
DNAME  ACCOUNTING                              
LOC    NEW YORK                                
DEPTNO 20                                      
DNAME  RESEARCH                                
LOC    DALLAS                                  
DEPTNO 30                                      
DNAME  SALES                                   
LOC    CHICAGO                                 
DEPTNO 40                                      
DNAME  OPERATIONS                              
LOC    BOSTON                                  
 
12 rows selected. 

Paulzip

Keith Laker, Oracle's Data Warehouse Product Management, even says what I'm saying in the Oracle blog on SQL Macros says the following :

1) don’t concatenate function parameter into the result string like

RETURN 'UPPER('||p_param||')'

or you’ll get ORA-64626: invalid SQL text returned from SQL macro: ORA-00909: invalid number of arguments

2) don’t use parameter as bind variable in a result string like

RETURN 'UPPER(:p_param)'

or you’ll get ORA-64625: bind variables not allowed in the string returned from SQL macro

just reference the parameter in a literal string to be returned, you can optionally prefix it with a function name if conflicting with other names

RETURN 'UPPER(new_func.p_param)'

this reference will be replaced with column name, bind variable, function or literal, whatever was used to invoke a sql macro function

Solomon Yakobson

Well, "It's a new, simpler way to encapsulate complex processing logic directly within SQL. SQL Macros allow developers to encapsulate complex processing within a new structure called a "macro" which can then be used within SQL statement". So where is that " complex processing within a new structure called a macro" can be encapsulated? Within generated SQL? That means "complex processing" will be repeated even if we pass same parameter value while from what I assumed I apply complex logic in macro and produce resulting SQL statement. This way when I call macro with same parameter value resulting SQL is readily available (well, as long as SQL didn't age out of shared pool). And all works well with non-string parameters.

SY.

Solomon Yakobson

Assume I have temperature readings table:

DROP TABLE TEMP_READINGS PURGE
/
CREATE TABLE TEMP_READINGS
  AS
    SELECT  EMPNO TEMP_F
      FROM  EMP
/

If I create the following SQL MACRO:

CREATE OR REPLACE
  FUNCTION TEMP_READINGS_MACRO(
                               P_SCALE VARCHAR2
                              )
    RETURN VARCHAR2 SQL_MACRO
    IS
    BEGIN
        RETURN Q'[SELECT CASE TEMP_READINGS_MACRO.P_SCALE WHEN 'F' THEN TEMP_F || 'F' ELSE ((TEMP_F − 32) * 5/9) || 'C' END TEMP FROM TEMP_READINGS]';
END;
/

Then CASE will be executed each time I run

SELECT * FROM TEMP_READINGS_MACRO('F');
or
SELECT * FROM TEMP_READINGS_MACRO('C');

While I'd prefer to check TEMP_READINGS_MACRO.P_SCALE and generate different selects based on parameter value:

CREATE OR REPLACE
  FUNCTION TEMP_READINGS_MACRO(
                               P_SCALE VARCHAR2
                              )
    RETURN VARCHAR2 SQL_MACRO
    IS
    BEGIN
        IF TEMP_READINGS_MACRO.P_SCALE = 'F'
          THEN
            RETURN Q'[SELECT TEMP_F || 'F' TEMP FROM TEMP_READINGS]';
        ELSIF TEMP_READINGS_MACRO.P_SCALE = 'C'
          THEN
            RETURN Q'[SELECT ((TEMP_F − 32) * 5/9) || 'C' TEMP FROM TEMP_READINGS]';
          ELSE
            RAISE_APPLICATION_ERROR(-20500,'Invalid scale.');
        END IF;
END;
/

And it will fail because for whatever reason macro has issue with string parameters:

SQL> SELECT * FROM TEMP_READINGS_MACRO('C');
SELECT * FROM TEMP_READINGS_MACRO('C')
              *
ERROR at line 1:
ORA-62565: The SQL Macro method failed with error(s).
ORA-20500: Invalid scale.
ORA-06512: at "SY47755.TEMP_READINGS_MACRO", line 14
ORA-06512: at line 5




SQL>

But if I use NUMBER parameter for scale:

CREATE OR REPLACE
  FUNCTION TEMP_READINGS_MACRO(
                               P_SCALE NUMBER
                              )
    RETURN VARCHAR2 SQL_MACRO
    IS
    BEGIN
        IF TEMP_READINGS_MACRO.P_SCALE = 1
          THEN
            RETURN Q'[SELECT TEMP_F || 'F' TEMP FROM TEMP_READINGS]';
        ELSIF TEMP_READINGS_MACRO.P_SCALE = 2
          THEN
            RETURN Q'[SELECT ((TEMP_F − 32) * 5/9) || 'C' TEMP FROM TEMP_READINGS]';
          ELSE
            RAISE_APPLICATION_ERROR(-20500,'Invalid scale.');
        END IF;
END;
/


Function created.


SQL> SELECT * FROM TEMP_READINGS_MACRO(1);


TEMP
-----------------------------------------
7369F
7499F
7521F
7566F
7654F
7698F
7782F
7788F
7839F
7844F
7876F
7900F
7902F
7934F


14 rows selected.


SQL>

SY.

Paulzip

Perhaps you don't think the ability to create things like parameterised views without sys contexts or package variables, without having to resort to PL/SQL (and context switching) and in a way that is transparent to the optimiser, so it can fully utilise the CBOs potential isn't powerful? The fact the caller only sees something like

select * from TableMacro(<parameters>)

..but the hidden behaviour could be a huge block of SQL (with complex parameter driven logic that is encapsulated within that call) isn't powerful?

That means "complex processing" will be repeated even if we pass same parameter value

Why would it? As I understand it, it is resolved to a parameterised piece of SQL once at parse time. Then effectively "find" -> "replaced" to all occurrences transparently, in a manner that appears to be the evaluated SQL to the optimiser. So it will be cached just like a block of SQL with bind parameters is.

Solomon Yakobson

Why would it? As I understand it, it is resolved to a parameterised piece of SQL once at parse time.

What is resolved at parse time? Look at my last example. My "complex logic" is checking temperature scale:

CREATE OR REPLACE
  FUNCTION TEMP_READINGS_MACRO(
                               P_SCALE VARCHAR2
                              )
    RETURN VARCHAR2 SQL_MACRO
    IS
    BEGIN
        RETURN Q'[SELECT CASE TEMP_READINGS_MACRO.P_SCALE WHEN 'F' THEN TEMP_F || 'F' ELSE ((TEMP_F − 32) * 5/9) || 'C' END TEMP FROM TEMP_READINGS]';
END;
/

And case is embedded into SELECT macro returns meaning CASE will be executed each time I select from SQL macro regardless what parameter value I pass. In addition SQL macro is always executed under definer rights meaning all code in that macro is executed under SQL macro owner's security domain while SELECT statement returned by the macro is executed under invoker's security domain so if complex logic would be, for example, selecting from SQL macro owner's owned table we will not be able to embedd such logic into SELECT macro retirns without granting invoker not just execute on the macro but also on all macro owner's owned objects used by complex logic - not good. Anyway, the fact it works for non-string parameters makes me believe there is a bug.

SY.

Solomon Yakobson

Anyway, I just opened SR. Will keep you posted.

SY.