spool with column headers in pipe delimited sqlplus

lightweight picture lightweight · Aug 20, 2013 · Viewed 44.2k times · Source

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?

Answer

Daniel Hári picture Daniel Hári · May 27, 2016
set heading on
set pagesize 0 embedded on
  • Includes header
  • Infinite page size