Oracle SQL_MACRO返回变量存储字符串时运行报错问题咨询

你对Oracle SQL宏的返回值规则、PL/SQL的q引号语法存在核心误解：

• q'{...}' 只是PL/SQL提供的字符串字面量写法，作用是编写字符串时不用手动转义内部单引号，q'{和}'是代码层面的语法标记，不会成为字符串本身的内容。第一个可正常运行的示例里，函数实际返回的字符串就是纯SQL文本select 1 from dual，不包含任何q引号相关的字符。
• SQL宏的核心要求是：函数直接返回可直接执行的纯SQL文本片段，不需要额外加任何字符串格式包装。

各版本报错的具体原因 #

1. 第一次替换为变量的写法报错ORA-01756: quoted string not properly terminated：
   你把变量名query直接写在了q字符串的大括号内部，PL/SQL会把它当成普通文本处理，不会解析为变量，同时你写的q字符串没有正确闭合边界，直接触发了语法错误。
2. 手动拼接ret变量的写法报错ORA-64626/ORA-00903：
   你画蛇添足把q引号的语法标记q'{、}'拼到了返回值里，SQL宏拿到返回内容后，会直接把这段文本拼到执行SQL中，等价于执行select count(*) from q'{ select 1 from dual }'，Oracle会把q'{识别为表名，自然触发无效表名错误。你单独测试ret返回值看到带q'{}'的内容，恰恰说明你的返回值格式不符合要求，不是可直接执行的纯SQL文本。

直接返回存储了SQL文本的变量即可，不需要额外包装q引号格式：

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()

如果需要动态拼接SQL片段，直接拼接纯SQL文本即可，q引号仅在你编写PL/SQL内的字符串字面量时使用，不要把q引号的边界符加入返回给SQL宏的文本中，示例：

with FUNCTION f_test(p_col_name varchar2) return varchar2 SQL_MACRO is
      query   VARCHAR2 (100) := 'select ' || p_col_name || ' from dual';
BEGIN
  RETURN query;
END;
select f_test('dummy') as col_val from dual

内容的提问来源于stack exchange，提问作者Pierre-olivier Gendraud