spool with column headers in pipe delimited sqlplus

I currently am spooling a pipe file via this sqlplus script:

    set feedback off
    set echo off
    set verify off
    set pagesize 0
    set heading off
    set termout off
    set trim on
    set wrap on
    set trimspool on
    set linesize 9000
    spool c:\exp3.txt
    select
    to_char(D_DTM, 'mm-dd-yyyy hh24.mi.ss')||'|'||
    DAYOFWEEK||'|'||"24HOUR"||'|'||TECHNOLOGY||'|'||VOICEDATA||'|'||MRKT_NM||'|'||REGION_NM||'|'||CLUSTER_NM||'|'||
    CLUSTER2_NM||'|'||BSC_NM||'|'||BTS_ID||'|'||BSC_BTS||'|'||CSCD_ID||'|'||CSCD_NM||'|'||SECT_SEQ_ID||'|'||BND_ID||'|'||
    FA_ID||'|'||ATT_CNT||'|'||AXS_F_CNT||'|'||CE_BLK_CNT||'|'||CUST_BLK_CNT||'|'||DRP_CALL_CNT||'|'||HHI_ATT_CNT||'|'||
    HHI_BAFRM_CNT||'|'||HHI_CALL_SETUP_SXS_CNT||'|'||MBL_ORG_CNT||'|'||MBL_TER_CNT||'|'||NON_BTS_EQ_BLK_CNT||'|'||
    PRIM_CALL_ERL||'|'||PWR_BLK_CNT||'|'||SFUL_CALL_CNT||'|'||SILENT_RETRY_CNT||'|'||T1_BHL_BLK_CNT||'|'||WCD_BLK_CNT||'|'||
    SMS_ATT_CNT||'|'||SMS_SXS_CNT||'|'||CTRL_CH_USG_CNT||'|'||CTRL_SL_USG_CNT||'|'||DO_SECT_PHL_FWD_PS_TMS||'|'||
    DO_SECT_PHL_REV_PS_TMS||'|'||EUSR_CONN_SETUP_ATT_CNT||'|'||EUSR_CONN_SETUP_F_CNT||'|'||FWD_D_TRANSD_QTY||'|'||
    MAC_ID_BLK_CNT||'|'||MAC_ID_UTIL_RT||'|'||MS_RQST_CNT||'|'||MS_RQST_D_QTY||'|'||NORM_CONN_CLS_CNT||'|'||
    NORM_SESS_RLS_CNT||'|'||RAB_SET_CNT||'|'||RCVD_RAB_CNT||'|'||REV_AIR_PER_BAD_FRM_CNT||'|'||REV_AIR_PER_TRSF_D_QTY||'|'||
    REV_D_TRANSD_QTY||'|'||RNC_BLK_CNT||'|'||SESS_ATT_CNT||'|'||SESS_CONF_SXS_CNT||'|'||SL_USG_CNT||'|'||MAX_USER_CNT||'|'||
    AVG_USER_CNT||'|'||MOU_TMS||'|'
    from ds3r_fh_all_fa_lvl_kpi
    where D_DTM = to_date('8/19/2013', 'mm/dd/yyyy');

but I can't figure out how to include the column names as the header in the file as well. How do I do that?

Solution

You should set the pagesize to a non-zero number that way your headers will be displayed. The number you set it to may depend on the number of rows that you have. If you set it 100 as per the following line:

set pagesize 100

then 100 rows will be printed under the headers, then the column headers will be repeated for the next 100 rows. The maximum as mentioned in 50000. Also set heading to on as

set heading on

otherwise it will not display the headers, despite the pagesize being non-zero. Also remember when you are selecting columns in that way they will not be padded, so the headings might appear out of place.