By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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
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
with FUNCTION f_test return varchar2 SQL_MACRO is
BEGIN
RETURN q'{
select 1 from dual
}';
END;
select count(*) from f_test()
| COUNT(*) |
|---|
| 1 |
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
| F_TEST() |
|---|
| q'{ select 1 from dual }' |