title: Format Column Headers during Pipe Delimited Concatenation (Oracle SQL)
body: <p><strong>Overview:</strong> 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.</p>
  
  <p>Consider the following five columns that I need to output:</p>
  
  <pre><code>SELECT
      a.USER_NAME || '|'
      || a.LAST_NAME || '|'
      || a.FIRST_NAME || '|'
      || b.PRODUCT_PURCHASED || '|'
      || c.DATEPURCHASED
  FROM ...
  WHERE ... ;
  </code></pre>
  
  <p>This SQL works fine, where the output looks like:</p>
  
  <pre><code>omnusruthius|ruthius|omnus|stackoverflow_prod|19-APR-16
  </code></pre>
  
  <p>However, the business wants it to look like:</p>
  
  <pre><code>USER_NM|LAST|FIRST|PROD|EFFECTIVE_DATE
  omnusruthius|ruthius|omnus|stackoverflow_prod|19-APR-16
  </code></pre>
  
  <p><strong>Problem:</strong> 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:</p>
  
  <pre><code>    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 ...
  </code></pre>
  
  <p>Unfortunately, I receive:</p>
  
  <blockquote>
    <p>ORA-00923: FROM keyword not found where expected</p>
  </blockquote>
  
  <p>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.</p>
  
  <p>So then I tried:</p>
  
  <pre><code>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 ...
  </code></pre>
  
  <p>Which gives the following error:</p>
  
  <blockquote>
    <p>ORA-01789: query block has incorrect number of result columns</p>
  </blockquote>
  
  <p>I feel so close to the solution, what am I missing here? Any help will be appreciated!</p>
  
  <p><strong>Edit:</strong> Just a note to a future reader, both answers here will help you solve this problem, but upon further tweaking, I realize we've all been overthinking the solution. I'm not going to propose a new solution as the change is trivial, but consider doing the following instead:</p>
  
  <pre><code>SELECT 'USER_NM|LAST|FIRST|PROD|EFFECTIVE_DATE' FROM DUAL;
  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 ... ;
  </code></pre>
  
  <p><strong>The key here is the use of the semicolons in SQL*Plus</strong>. That first SELECT statement is completely independent from the second; no UNION is necessary as the output of the first query is automatically displayed immediately before the output of the second query. Both can have their own rules, which is especially convenient if your latter query is much more complicated. I can confirm the above query is working, and I'm surprised it took me that long to make that realization...</p>