Loading data into CSV files

[sanjeet]

I am trying to load some data into CSV files but I get this error message:

ORA-00922, missing or invalid option.

set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool AdvisorClientGroupFact.txt
select MONTH_DSI_ID || ',' || GRP_DSI_ID || ',' ||ADV_DSI_ID || ',' || GRP_STAT_DSI_ID || ',' || VALUE_TIER_DSI_ID || ',' || PLATFM_DSI_ID || ',' || FP_STAT_DSI_ID || ',' || ORG_DSI_ID || ',' || YEAR_NBR || ',' || ADV_NBR || ',' || MONTH_NBR || ',' || GRP_STAT_CD || ',' || GRP_STAT_DESC || ',' || VALUE_TIER_CD || ',' || VALUE_TIER_DESC || ',' || SUMMARY_TIER_CD || ',' || SUMMARY_TIER_DESC || ',' || PLATFM_CD || ',' || PLATFM_NME || ',' || FP_STAT_CD || ',' || FP_STAT_DESC || ',' || GRP_NET_WORTH_CD || ',' || ACQ_ROLLING_12MO_HVC_IND || ',' || HVC_IND || ',' || HPC_IND || ',' ||GRP_ACT_ACCTS_CAMS_CNT || ',' || GRP_DRV_CASH_VAL_AMT || ',' || GRP_DRV_TOT_VAL_AMT || ',' || GRP_GDC_YTD_TOS_AMT || ',' || GRP_GDC_YTD_TOT_AMT || ',' || GRP_IAP_MAX_VAL || ',' || GRP_DRV_ANL_INCM_AMT || ',' || GRP_LOR_VAL|| ',' || NBR_PROD_TYPES_CNT || ',' || NOTPPT_TOT_VAL_AMT || ',' || QUAL_TOT_VAL_AMT || ',' || VAR_ANNTY_TOT_VAL_AMT || ',' || FX_ANNTY_TOT_VAL_AMT || ',' || FUNDS_PROP_TOT_VAL_AMT || ',' || BRKR_PROP_TOT_VAL_AMT || ',' || BRKR_NON_PROP_TOT_VAL_AMT || ',' || BRKR_OTHER_TOT_VAL_AMT || ',' || SPS_PROP_TOT_VAL_AMT || ',' || SPS_NON_PROP_TOT_VAL_AMT || ',' || SPS_OTHER_TOT_VAL_AMT || ',' || PREMIER_PROP_TOT_VAL_AMT || ',' || PREMIER_NON_PROP_TOT_VAL_AMT || ',' || DIR_INV_TOT_VAL_AMT || ',' || OTHER_TOT_VAL_AMT || ',' || CERTS_TOT_VAL_AMT || ',' || INS_TOT_VAL_AMT || ',' || ONE_ACCOUNT_STATUS_IND || ',' || NEW_GRP_IND || ',' || ART_FP_ACTV_STATUS_IND || ',' || POST_DTS
from T_ADVISOR_CLIENT_GROUP_FACT
where grp_dsi_id < 200000010000
spool off

[sanjeet]

I added a ; works now.

Ok I tried this in SQL PLUS and it works only thing is it wraps in multiple lines and not just one line.

Is there a way to have the data to be displayed in one line and not in multiple lines?
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool AdvisorClientGroupFact.txt
select MONTH_DSI_ID || ',' || GRP_DSI_ID || ',' ||ADV_DSI_ID || ',' || GRP_STAT_DSI_ID || ',' || VALUE_TIER_DSI_ID || ',' || PLATFM_DSI_ID || ',' || FP_STAT_DSI_ID || ',' || ORG_DSI_ID || ',' || YEAR_NBR || ',' || ADV_NBR || ',' || MONTH_NBR || ',' || GRP_STAT_CD || ',' || GRP_STAT_DESC || ',' || VALUE_TIER_CD || ',' || VALUE_TIER_DESC || ',' || SUMMARY_TIER_CD || ',' || SUMMARY_TIER_DESC || ',' || PLATFM_CD || ',' || PLATFM_NME || ',' || FP_STAT_CD || ',' || FP_STAT_DESC || ',' || GRP_NET_WORTH_CD || ',' || ACQ_ROLLING_12MO_HVC_IND || ',' || HVC_IND || ',' || HPC_IND || ',' ||GRP_ACT_ACCTS_CAMS_CNT || ',' || GRP_DRV_CASH_VAL_AMT || ',' || GRP_DRV_TOT_VAL_AMT || ',' || GRP_GDC_YTD_TOS_AMT || ',' || GRP_GDC_YTD_TOT_AMT || ',' || GRP_IAP_MAX_VAL || ',' || GRP_DRV_ANL_INCM_AMT || ',' || GRP_LOR_VAL|| ',' || NBR_PROD_TYPES_CNT || ',' || NOTPPT_TOT_VAL_AMT || ',' || QUAL_TOT_VAL_AMT || ',' || VAR_ANNTY_TOT_VAL_AMT || ',' || FX_ANNTY_TOT_VAL_AMT || ',' || FUNDS_PROP_TOT_VAL_AMT || ',' || BRKR_PROP_TOT_VAL_AMT || ',' || BRKR_NON_PROP_TOT_VAL_AMT || ',' || BRKR_OTHER_TOT_VAL_AMT || ',' || SPS_PROP_TOT_VAL_AMT || ',' || SPS_NON_PROP_TOT_VAL_AMT || ',' || SPS_OTHER_TOT_VAL_AMT || ',' || PREMIER_PROP_TOT_VAL_AMT || ',' || PREMIER_NON_PROP_TOT_VAL_AMT || ',' || PREMIER_OTHER_TOT_VAL_AMT || ',' || DIR_INV_TOT_VAL_AMT || ',' || OTHER_TOT_VAL_AMT || ',' || CERTS_TOT_VAL_AMT || ',' || INS_TOT_VAL_AMT || ',' || ONE_ACCOUNT_STATUS_IND || ',' || NEW_GRP_IND || ',' || ART_FP_ACTV_STATUS_IND || ',' || POST_DTS
from T_ADVISOR_CLIENT_GROUP_FACT
where grp_dsi_id < 200000010000;
spool off

[mdsami]

Instead of all these hard work you can better use third party tools on the internet one such tool is Data Loader found at www.dbload.com

Ashi