William R William R - 6 months ago 26
Bash Question

How to Generate Sqlplus to csv file?

i am trying to generate the csv file out of oracle SQLPLUS by shell script, I am getting headers repeated multiple times and also null values are coming at the end of the line,

echo "****************************************************************"

echo "Started may at : " $(date +"%r")

sqlplus -s ETLUSER/ETL@//10.135.23.232:1521/ODS<<EOF >/dev/null

SPOOL wfa_may.csv

SELECT MAC_ADDRESS ||','||
CRM_BP_ID || ',' ||
EVENT_TIMESTAMP || ',' ||
ACCT_INPUT_OCTETS || ',' ||
ACCT_OUTPUT_OCTETS || ',' ||
ACCT_INPUT_PACKETS || ',' ||
ACCT_OUTPUT_PACKETS || ',' ||
ACCT_SESSION_TIME || ',' ||
NAS_IDENTIFIER || ',' ||
EXTRACTION_DATE || ',' ||
RECORD_DATE || ',' ||
LAST_MODIFIED_DATE || ',' ||
ETL_BATCH_ID || ',' ||
ETL_PROCESS_ID || ',' ||
RECORD_LOAD_TIMESTAMP FROM DBAS.S_WFA_ACCOUNTING_MSG where to_char(EVENT_TIMESTAMP,'MON') = 'MAY';

exit

EOF

echo "Ended may at : " $(date +"%r")

echo "****************************************************************"


from NAS_IDENTIFIER i am not able to find any values displayed in file.

Can anyone help me whats wrong ?

Answer

Use either set head off or set pages 0 in the script.

Comments