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?
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