Home » SQL & PL/SQL » SQL & PL/SQL » Loading data into CSV files
Loading data into CSV files [message #194868] Mon, 25 September 2006 15:54 Go to next message
Sanjeet1
Messages: 4
Registered: September 2006
Junior Member
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
Re: Loading data into CSV files [message #194872 is a reply to message #194868] Mon, 25 September 2006 16:36 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
you need a ";" after the select. Also - you should explicitly do a to_char() with the appropriate format spec. for all date, numeric columns. Also you need to set LONG to a suitably big number to aviod truncation. If you don't mind fixed format output columns, you can also just:

set colsep ','
select MONTH_DSI_ID, GRP_DSI_ID...
from T_ADVISOR_CLIENT_GROUP_FACT...
Re: Loading data into CSV files [message #194873 is a reply to message #194872] Mon, 25 September 2006 16:47 Go to previous messageGo to next message
Sanjeet1
Messages: 4
Registered: September 2006
Junior Member
Ok I did add the ; but I got the same error. How do I convert all the fields to char?

Thanks in advance.

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
Re: Loading data into CSV files [message #195018 is a reply to message #194868] Tue, 26 September 2006 10:29 Go to previous messageGo to next message
Sanjeet1
Messages: 4
Registered: September 2006
Junior Member
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?
Re: Loading data into CSV files [message #195025 is a reply to message #195018] Tue, 26 September 2006 11:55 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
set linesize 500
set trimspool on
set pagesize 0
Previous Topic: Problem with Group by rollup query
Next Topic: ORA-01841 error in trigger date validation.
Goto Forum:
  


Current Time: Sun Dec 04 08:37:07 CST 2016

Total time taken to generate the page: 0.05118 seconds