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

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

CodePudding user response：

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