How to write a SQL statement that spools the result of select, replace and order by with custom delimiters to a csv file

I am trying to do exactly what the question says: selecting from a table, replacing special characters from a column, order the result, then spool to a .csv file, with a custom delimiter.

I can only use sqlplus for this and have the script saved into a file, calling the file by @filename.

The following is what I have that is working without the order by clause

SPOOL 'comparisonPrdIZ/TB_PROV_HEADER_PART1.csv';
SELECT '"SERIAL_NO"<Sep>"PROV_HEADER"' FROM DUAL
UNION ALL
SELECT '"' ||SERIAL_NO|| '"<Sep>"'
||REPLACE(REPLACE(REPLACE(SUBSTR(PROV_HEADER,1,2000), CHR(10), ''), CHR(34),
'inchORspace'), CHR(44), '')|| '"' 
FROM TABLE;
SPOOL OFF;

and what does not work and throws an error

ORA-00904: "SERIAL_NO": invalid identifier error

SPOOL 'comparisonPrdIZ/TB_PROV_HEADER_PART1.csv';
SELECT '"SERIAL_NO"<Sep>"PROV_HEADER"' FROM DUAL
UNION ALL
SELECT '"' ||SERIAL_NO|| '"<Sep>"'
||REPLACE(REPLACE(REPLACE(SUBSTR(PROV_HEADER,1,2000), CHR(10), ''), CHR(34),
'inchORspace'), CHR(44), '')|| '"' 
FROM TABLE 
ORDER BY SERIAL_NO;
SPOOL OFF;

Solution

You're trying to order the overall results of the UNION, not just the query in the second branch. The union query does not have a column called SERIAL_NO - the second branch run on its own does, but once it's in a union it does not. It is effectively:

SELECT *
FROM (
  SELECT '"SERIAL_NO"<Sep>"PROV_HEADER"' FROM DUAL
  UNION ALL
  SELECT '"' ||SERIAL_NO|| '"<Sep>"'
  ||REPLACE(REPLACE(REPLACE(SUBSTR(PROV_HEADER,1,2000), CHR(10), ''),   CHR(34),
  'inchORspace'), CHR(44), '')|| '"' 
  FROM TABLE 
)
ORDER BY SERIAL_NO;

So only the column expression is available for ordering. But you probably don't want to do that anyway, as your header row would be included in the sort. You can make it work, e.g. if SERIAL_NO can't be null:

SELECT RESULT
FROM (
  SELECT NULL AS SERIAL_NO,
    '"SERIAL_NO"<Sep>"PROV_HEADER"' AS RESULT
  FROM DUAL
  UNION ALL
  SELECT SERIAL_NO,
    '"' ||SERIAL_NO|| '"<Sep>"'
      ||REPLACE(REPLACE(REPLACE(SUBSTR(PROV_HEADER,1,2000), CHR(10), ''), CHR(34),
      'inchORspace'), CHR(44), '')|| '"'
  FROM TABLE
)
ORDER BY SERIAL_NO NULLS FIRST;

fiddle, also with an option if that can be null.

But as you're in SQL*Plus there are two much simpler options.

Either run two queries:

SELECT '"SERIAL_NO"<Sep>"PROV_HEADER"' FROM DUAL;

SELECT '"' ||SERIAL_NO|| '"<Sep>"'
||REPLACE(REPLACE(REPLACE(SUBSTR(PROV_HEADER,1,2000), CHR(10), ''), CHR(34),
'inchORspace'), CHR(44), '')|| '"' 
FROM TABLE 
ORDER BY SERIAL_NO;

Or use prompt for the header row:

PROMPT "SERIAL_NO"<Sep>"PROV_HEADER"

SELECT '"' ||SERIAL_NO|| '"<Sep>"'
||REPLACE(REPLACE(REPLACE(SUBSTR(PROV_HEADER,1,2000), CHR(10), ''), CHR(34),
'inchORspace'), CHR(44), '')|| '"' 
FROM TABLE 
ORDER BY SERIAL_NO;

You may need to set embed on to avoid a blank line between them; and you might want to run sqlplus with the -s 'silent' flag.