sqloracle-databaseoracle21c

How to use a macro such as the returned string is a variable?


it works:

with FUNCTION f_test return varchar2 SQL_MACRO is
BEGIN
  RETURN q'{
       select 1 from dual
  }';
END;
select count(*) from f_test()

But if replace the query by a string it doesn't work anymore

with FUNCTION f_test return varchar2 SQL_MACRO is
      query   VARCHAR2 (100) := 'select 1 from dual';
BEGIN
  RETURN q'{
             query
             '};
END;
select count(*) from f_test()

ORA-01756: quoted string not properly terminated

I've tried a little bit differently

with FUNCTION f_test return varchar2 SQL_MACRO is
      query   VARCHAR2 (100) := 'select 1 from dual';
      ret     VARCHAR (100) := 'q''{' || chr(13) || query || chr(13) || '}''';
BEGIN
  RETURN ret;
END;
select count(*) from f_test()

ORA-64626: invalid SQL text returned from SQL macro: ORA-00903: invalid table name

I've test the value of ret

with FUNCTION f_test return varchar2  is
      query   VARCHAR2 (100) := 'select 1 from dual';
      ret     VARCHAR (100) := 'q''{' || chr(13) || query || chr(13) || '}''';
BEGIN
  RETURN ret;
END;
select   f_test() from dual

q'{ select 1 from dual }'

it should work. It's the expected string.

Do I have made a syntax error or is it impossible to create a macro if the returned string is a variable?

code


Solution

  • Your second function has q'{...'} when it should be q'{...}':

    with FUNCTION f_test return varchar2 SQL_MACRO is
          query   VARCHAR2 (100) := 'select 1 from dual';
    BEGIN
      RETURN q'{query}';
    END;
    select count(*) from f_test()
    

    However, that still won't work as q-quoted strings are string literals and not template strings and will not embed the variable in-place of the string literal; the query variable is unused and the function is effectively:

    with FUNCTION f_test return varchar2 SQL_MACRO is
    BEGIN
      RETURN q'{query}';
    END;
    select count(*) from f_test()
    

    Which is the same as:

    select count(*) from query
    

    This gives you the error:

    ORA-00942: table or view does not exist
    

    As there is no table named query.

    If you want to pass a table name then:

    with FUNCTION f_test return varchar2 SQL_MACRO is
    BEGIN
      RETURN 'DUAL';
    END;
    select count(*) from f_test()
    

    Your third query is returning a string literal that contains the text formatted as a q-quoted string literal.

    So the query is effectively:

    select count(*) from q'{
    select 1 from dual
    }'
    

    Which gives the error:

    ORA-00903: invalid table name
    

    You want to not use a q-quoted string and just use concatenation:

    with FUNCTION f_test return varchar2 SQL_MACRO is
      query VARCHAR2(100) := 'select 1 from dual';
      ret   VARCHAR2(100) := chr(13) || query || chr(13);
    BEGIN
      RETURN ret;
    END;
    select count(*) from f_test()
    

    db<>fiddle here