如何让PL/SQL代码输出''而非'？

我写了一段PL/SQL逻辑，想把字符替换为空字符串（''），代码如下：

DECLARE
  v_owner VARCHAR2(50) := 'LFS_SURVEY'; -- 替换为你的schema/用户名
  v_table_name VARCHAR2(50) := 'LFS_FAMILY_TAB'; -- 替换为你的表名
  v_rp_ids VARCHAR2(100) := '2030, 2031'; -- 替换为目标RP_ID值
  v_columns CLOB;
  v_query   CLOB;
BEGIN
  -- 根据数据类型获取所有列名并生成转换逻辑
EXECUTE IMMEDIATE 'SELECT RTRIM(XMLAGG(XMLELEMENT(e, 
    CASE 
      WHEN COLUMN_NAME = ''F_M_ID'' THEN ''TO_CHAR(t.F_M_ID) AS F_M_ID''
      WHEN DATA_TYPE LIKE ''%CHAR%'' THEN ''REPLACE(REPLACE(t.'' || COLUMN_NAME || '', CHR(13), ''''), CHR(10), '''') AS '' || COLUMN_NAME
      ELSE ''t.'' || COLUMN_NAME 
    END || '', '').EXTRACT(''//text()'') ORDER BY COLUMN_ID).GETCLOBVAL(), '', '') 
    FROM ALL_TAB_COLUMNS 
    WHERE TABLE_NAME = ''' || v_table_name || ''' 
    AND OWNER = ''' || v_owner || '''' 
    INTO v_columns;
  DBMS_OUTPUT.PUT_LINE(v_columns);
END;
/

但输出结果里的空字符串变成了'，示例如下：

REPLACE(REPLACE(t.Q_851, CHR(13), '), CHR(10), ') AS Q_851

怎么让代码强制输出''？

问题根源是XMLELEMENT会自动对XML特殊字符做转义，单引号'会被转成'，最终输出因为二次转义变成了'。可以用两种方式解决：

方法一：用DBMS_XMLGEN.convert反转义 #

在获取XML聚合结果后，调用DBMS_XMLGEN.convert函数执行反转义，把转义后的字符还原成原始单引号。修改后的代码如下：

DECLARE
  v_owner VARCHAR2(50) := 'LFS_SURVEY'; -- 替换为你的schema/用户名
  v_table_name VARCHAR2(50) := 'LFS_FAMILY_TAB'; -- 替换为你的表名
  v_rp_ids VARCHAR2(100) := '2030, 2031'; -- 替换为目标RP_ID值
  v_columns CLOB;
  v_query   CLOB;
BEGIN
  -- 根据数据类型获取所有列名并生成转换逻辑，添加反转义处理
EXECUTE IMMEDIATE 'SELECT DBMS_XMLGEN.convert(RTRIM(XMLAGG(XMLELEMENT(e, 
    CASE 
      WHEN COLUMN_NAME = ''F_M_ID'' THEN ''TO_CHAR(t.F_M_ID) AS F_M_ID''
      WHEN DATA_TYPE LIKE ''%CHAR%'' THEN ''REPLACE(REPLACE(t.'' || COLUMN_NAME || '', CHR(13), ''''), CHR(10), '''') AS '' || COLUMN_NAME
      ELSE ''t.'' || COLUMN_NAME 
    END || '', '').EXTRACT(''//text()'') ORDER BY COLUMN_ID).GETCLOBVAL(), '', ''), 1) 
    FROM ALL_TAB_COLUMNS 
    WHERE TABLE_NAME = ''' || v_table_name || ''' 
    AND OWNER = ''' || v_owner || '''' 
    INTO v_columns;
  DBMS_OUTPUT.PUT_LINE(v_columns);
END;
/

这里DBMS_XMLGEN.convert的第二个参数传1，表示执行反转义操作，会把'还原成'，最终输出就能得到''。

方法二：改用LISTAGG拼接（列数量不多时适用） #

如果表的列数不多，不会超过LISTAGG的长度限制（默认是VARCHAR2的32767字节），可以直接用LISTAGG代替XMLAGG，避免XML转义的问题：

DECLARE
  v_owner VARCHAR2(50) := 'LFS_SURVEY'; -- 替换为你的schema/用户名
  v_table_name VARCHAR2(50) := 'LFS_FAMILY_TAB'; -- 替换为你的表名
  v_rp_ids VARCHAR2(100) := '2030, 2031'; -- 替换为目标RP_ID值
  v_columns VARCHAR2(32767); -- LISTAGG有长度限制，列多的话还是用方法一
  v_query   CLOB;
BEGIN
  -- 根据数据类型获取所有列名并生成转换逻辑
EXECUTE IMMEDIATE 'SELECT RTRIM(LISTAGG(
    CASE 
      WHEN COLUMN_NAME = ''F_M_ID'' THEN ''TO_CHAR(t.F_M_ID) AS F_M_ID''
      WHEN DATA_TYPE LIKE ''%CHAR%'' THEN ''REPLACE(REPLACE(t.'' || COLUMN_NAME || '', CHR(13), ''''), CHR(10), '''') AS '' || COLUMN_NAME
      ELSE ''t.'' || COLUMN_NAME 
    END, '', '') WITHIN GROUP (ORDER BY COLUMN_ID), '', '')
    FROM ALL_TAB_COLUMNS 
    WHERE TABLE_NAME = ''' || v_table_name || ''' 
    AND OWNER = ''' || v_owner || '''' 
    INTO v_columns;
  DBMS_OUTPUT.PUT_LINE(v_columns);
END;
/

如果表的列数量较多，超过LISTAGG的长度限制，优先选方法一。

内容的提问来源于stack exchange，提问作者ray