Forum Stats

  • 3,750,233 Users
  • 2,250,137 Discussions
  • 7,866,845 Comments

Discussions

19C: SQL Macro passed string parameters

Solomon Yakobson
Solomon Yakobson Member Posts: 18,650 Black Diamond

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.

«134

Comments

  • Paulzip
    Paulzip Member Posts: 8,406 Blue Diamond

    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
    Solomon Yakobson Member Posts: 18,650 Black Diamond
    edited Dec 30, 2020 4:09PM

    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
    Solomon Yakobson Member Posts: 18,650 Black Diamond
    edited Dec 30, 2020 4:15PM

    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
    Paulzip Member Posts: 8,406 Blue Diamond
    edited Dec 30, 2020 5:15PM

    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
    Paulzip Member Posts: 8,406 Blue Diamond

    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
    Solomon Yakobson Member Posts: 18,650 Black Diamond
    edited Dec 30, 2020 5:34PM

    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
    Solomon Yakobson Member Posts: 18,650 Black Diamond

    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
    Paulzip Member Posts: 8,406 Blue Diamond

    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
    Solomon Yakobson Member Posts: 18,650 Black Diamond

    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
    Solomon Yakobson Member Posts: 18,650 Black Diamond

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

    SY.