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 query;
END;
select count(*) from f_test()
| COUNT(*) |
|---|
| 1 |
with FUNCTION f_test return varchar2 SQL_MACRO is
BEGIN
RETURN q'{query}';
END;
select count(*) from f_test()
ORA-00942: table or view does not exist
with FUNCTION f_test return varchar2 SQL_MACRO is
BEGIN
RETURN 'DUAL';
END;
select count(*) from f_test()
| COUNT(*) |
|---|
| 1 |
with FUNCTION f_test return varchar2 SQL_MACRO is
BEGIN
RETURN q'{DUAL}';
END;
select count(*) from f_test()
| COUNT(*) |
|---|
| 1 |
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
select count(*) from q'{
select 1 from dual
}'
ORA-00903: invalid table name
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()
| COUNT(*) |
|---|
| 1 |