Omnus Ruthius Omnus Ruthius - 7 months ago 75
SQL Question

Format Column Headers during Pipe Delimited Concatenation (Oracle SQL)

Overview: I am tasked with providing a data extract from an Oracle database as a pipe-delimited output text file. I will be using SQLPlus to do this on the server where the data lives. Ordinarily, this task is not beyond my experience, but this time, the business desires column headers to be present.

Consider the following five columns that I need to output:

SELECT
a.USER_NAME || '|'
|| a.LAST_NAME || '|'
|| a.FIRST_NAME || '|'
|| b.PRODUCT_PURCHASED || '|'
|| c.DATEPURCHASED
FROM ...
WHERE ... ;


This SQL works fine, where the output looks like:

omnusruthius|ruthius|omnus|stackoverflow_prod|19-APR-16


However, the business wants it to look like:

USER_NM|LAST|FIRST|PROD|EFFECTIVE_DATE
omnusruthius|ruthius|omnus|stackoverflow_prod|19-APR-16


Problem: So the objective here is essentially to output the first row with custom-named column headers (aliases), as shown above. So my first approach was to try something like:

SELECT
a.USER_NAME AS USER_NM || '|'
|| a.LAST_NAME AS LAST || '|'
|| a.FIRST_NAME AS FIRST || '|'
|| b.PRODUCT_PURCHASED AS PROD || '|'
|| c.DATEPURCHASED AS EFFECTIVE_DATE
FROM ...
WHERE ...


Unfortunately, I receive:


ORA-00923: FROM keyword not found where expected


I'm not sure how that would help anyway, as the original SQL output without aliases does not show column headers in the first row anyway. Remember, this is through the command line (SQLPlus), not Toad or some other RDMS.

So then I tried:

SELECT
'USER_NM', 'LAST', 'FIRST', 'PROD', 'EFFECTIVE_DATE' FROM DUAL
UNION ALL
SELECT
a.USER_NAME || '|'
|| a.LAST_NAME || '|'
|| a.FIRST_NAME || '|'
|| b.PRODUCT_PURCHASED || '|'
|| c.DATEPURCHASED
FROM ...
WHERE ...


Which gives the following error:


ORA-01789: query block has incorrect number of result columns


I feel so close to the solution, what am I missing here? Any help will be appreciated!

Answer

Since you tagged the question with SQL*Plus, you can use the PROMPT command to generate the header, which avoids complications ordering the results with a union:

PROMPT USER_NM|LAST|FIRST|PROD|EFFECTIVE_DATE

SELECT
a.USER_NAME AS USER_NM || '|'
|| a.LAST_NAME AS LAST || '|'
|| a.FIRST_NAME AS FIRST || '|'
|| b.PRODUCT_PURCHASED AS PROD || '|'
|| c.DATEPURCHASED AS EFFECTIVE_DATE
FROM ...
WHERE ...
ORDER BY ...

That takes the headings out of the SQL and into the client realm, where it arguably belongs. This also works in SQL Developer, and other clients may be able to do something similar. It won't work if you run the query on its own from another client, or over JDBC, or whatever; but then whatever is running the query can (and maybe should) provide the header in that case too.

If you aren't already, you could also consider doing SET HEADING OFF or SET PAGESIZE 0 to suppress the column headings from the query itself (though from what you said you're already doing that); and possibly SET EMBED OFF, though I don't think that's needed unless you do a separate query to generate the header line.