user2061886 user2061886 - 6 months ago 14
SQL Question

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?

Answer

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

set pagesize 50000

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.

Comments