How to print '' instead of '

I have logic replacing characters with '':

DECLARE
  v_owner VARCHAR2(50) := 'LFS_SURVEY'; -- Replace with your schema/owner name
  v_table_name VARCHAR2(50) := 'LFS_FAMILY_TAB'; -- Replace with your table name
  v_rp_ids VARCHAR2(100) := '2030, 2031'; -- Replace with your RP_ID values
  v_columns CLOB;
  v_query   CLOB;
BEGIN
  -- Get all column names with transformations based on data type
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;
/

The output shows ' instead of '', e.g.:

REPLACE(REPLACE(t.Q_851, CHR(13), &appos;), CHR(10), &appos;) AS Q_851

How can I force it to print ''?

Solution

As an empty string '' is equivalent to null in Oracle, in this instance you could avoid the issue by replacing tab/newline with null instead; so rather than this:

WHEN DATA_TYPE LIKE ''%CHAR%''
THEN ''REPLACE(REPLACE(t.'' || COLUMN_NAME || '', CHR(13), ''''), CHR(10), '''') AS '' || COLUMN_NAME

do this:

WHEN DATA_TYPE LIKE ''%CHAR%''
THEN ''REPLACE(REPLACE(t.'' || COLUMN_NAME || '', CHR(13), null), CHR(10), null) AS '' || COLUMN_NAME

You don't need to use dynamic SQL here though, as you can statically query the data dictionary - the target schema/table name are variables here, not identifiers:

DECLARE
  v_owner VARCHAR2(50) := 'LFS_SURVEY'; -- Replace with your schema/owner name
  v_table_name VARCHAR2(50) := 'LFS_FAMILY_TAB'; -- Replace with your table name
  v_rp_ids VARCHAR2(100) := '2030, 2031'; -- Replace with your RP_ID values
  v_columns CLOB;
  v_query   CLOB;
BEGIN
  -- Get all column names with transformations based on data type
  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), null), CHR(10), null) AS ' || COLUMN_NAME
      ELSE 't.' || COLUMN_NAME 
    END || ', ').EXTRACT('//text()') ORDER BY COLUMN_ID).GETCLOBVAL(), ', ') 
  INTO v_columns
  FROM ALL_TAB_COLUMNS 
  WHERE TABLE_NAME = v_table_name
  AND OWNER = v_owner;
  DBMS_OUTPUT.PUT_LINE(v_columns);
END;
/

With a dummy table created as:

create table LFS_FAMILY_TAB (f_m_id number, foo date, bar varchar2(30))

that outputs:

TO_CHAR(t.F_M_ID) AS F_M_ID, t.FOO, REPLACE(REPLACE(t.BAR, CHR(13), null), CHR(10), null) AS BAR

fiddle

And that is functionally the same as

TO_CHAR(t.F_M_ID) AS F_M_ID, t.FOO, REPLACE(REPLACE(t.BAR, CHR(13), ''), CHR(10), '') AS BAR

If you really want '' instead of null (or perhaps have to deal with other text literals) then you can use XMLTable, which doesn't escape the entities like extract (and XMLQuery):

DECLARE
  v_owner VARCHAR2(50) := 'LFS_SURVEY'; -- Replace with your schema/owner name
  v_table_name VARCHAR2(50) := 'LFS_FAMILY_TAB'; -- Replace with your table name
  v_rp_ids VARCHAR2(100) := '2030, 2031'; -- Replace with your RP_ID values
  v_columns CLOB;
  v_query   CLOB;
BEGIN
  -- Get all column names with transformations based on data type
  SELECT RTRIM(x.str, ', ')
  INTO v_columns
  FROM (
    SELECT XMLELEMENT(r, 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 || ', ') ORDER BY COLUMN_ID)) AS xmlx
    FROM ALL_TAB_COLUMNS
    WHERE TABLE_NAME = v_table_name
    AND OWNER = v_owner
  ) atc
  CROSS JOIN XMLTABLE(
    '/R'
    PASSING atc.xmlx
    COLUMNS str CLOB PATH '.'
  ) x;
  DBMS_OUTPUT.PUT_LINE(v_columns);
END;
/

which for the same dummy table generates:

TO_CHAR(t.F_M_ID) AS F_M_ID, t.FOO, REPLACE(REPLACE(t.BAR, CHR(13), ''), CHR(10), '') AS BAR

fiddle

Incidentally, your original code produces ', but that's just one entity-escaped quote '. If you wanted two '' then you need even more in your code:

WHEN DATA_TYPE LIKE ''%CHAR%''
THEN ''REPLACE(REPLACE(t.'' || COLUMN_NAME || '', CHR(13), ''''''''), CHR(10), '''''''') AS '' || COLUMN_NAME

But as that would then produce '' it wouldn't actually solve your issue - though you then manually unescape those entities.

How to print '' instead of &apos;

How to print '' instead of '