I have a .SQL file with column only.i need to generate a csv file and all the columns should be in one row but the problem is the header portion is divided into many rows.what should i do right now to fix the problem?this sql file will be generated from a shell script.
This is my SQL file.
set heading off
set pagesize 0
set linesize 1000
set trimspool on
set feedback off
set colsep ","
column filename new_val filename
SELECT 'ReporteContratosFija_CBiO_'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')||'.csv' filename FROM dual;
--spool $REPORT_HOME/generated_reports/&filename
spool $REPORT_GEN_PATH/&filename
-- This SQL code retrieves from NK Table to generate MRC details after the bill run
select 'Customer id'||','||' Carry over period length'||','||'
Carry over period unit of measure'||','||' Small invoice omission amount value'||','||'
Small invoice omission amount currency'||','||' Family'||','||' Customer status'||','||'
Customer status reason'||','||' Customer status date'||','||' Customer level'||','||'
Parent customer'||','||' Is payment responsible?, Payment responsible'||','||'
Is contract responsible ?, Price group'||','||' OCC Rate plan'||','||' Customer Area'||','||'
Trade code'||','||' Cost center'||','||' Customer payable currency'||','||' Conversion rate type'||','||'
Customer tax category'||','||' Customer jurisdiction'||','||' Billing Cycle'||','||' Las bill cycle run for the customer'||','||'
Current balance'||','||'Previous balance'||','||' Unbilled amount'||','||' Collection indicator'||','||' Customer First name'||','||'
Customer Middle name'||','||' Customer Last name'||','||' Company name'||','||'Street'||','||' Street number'||','||' Postal code'||','||' City'||','||'
State'||','||' County'||','||' Country'||','||' Is employee ?, Job description'||','||' Phone number 1'||','||' Phone number 2'||','||' Fax'||','||' Email'||','||'
Mobile for SMS'||','||'Customer document'||','||' Customer document type'||','||' Customer nationality'||','||' Customer type'||','||'
Billing account code'||','||' Billing account name'||','||' Bill Medium'||','||' Billing account contact first name'||','||'
Billing account contact midle name'||','||' Billing account contact last name'||','||'
Billing account company name'||','||' Billing account postal street'||','||'
Billing account postal street number'||','||' Billing account postal code'||','||'
Billing account postal city'||','||' Billing account postal state'||','||'
Billing account postal county'||','||' Billing account postal country'||','||'
Billing account contact job description'||','||'
Billing account contact phone number 1'||','||'
Billing account contact phone number 2'||','||'
Billing account contact fax'||','||' Billing account contact email'||','||'
Billing account contact mobile for SMS'||','||' Billing account contact document'||','||'
Billing account contact document type'||','||' Billing account contact nationality'||','||'
BSCS Contract Id'||','||' Tinko Contract Id'||','||' Contract user'||','||'
Contract Charging system id'||','||' SDP ID'||','||' Contract signed date'||','||'
Contract installation date'||','||' Contract Creation Date'||','||'
Contract current status'||','||' Contract current status reason'||','||'
Contract current status valid from date'||','||'
Contract user first name'||','||'
Contract user middle name'||','||' Contract user last name'||','||'
Contract user company name'||','||' Contract installation postal street'||','||'
Contract installation postal street number'||','||' Contract installation postal code'||','||'
Contract installation postal city'||','||' Contract installation postal state'||','||'
Contract installation postal county'||','||' Contract installation postal country'||','||'
Contract user job description'||','||' Contract user contact phone number 1'||','||'
Contract user contact phone number 2'||','||' Contract user contact fax'||','||'
Contract user contact email'||','||' Contract user contact mobile for SMS'||','||'
Contract user document'||','||' Contract user document type'||','||'
Contract user nationality'||','||' PO Name'||','||' Product instance id'||','||'
Catalog offer id'||','||' CFSS ID'||','||' CFSS Name'||','||' RFSS ID'||','||' RFSS Name'||','||'
Offer type'||','||' PO Element'||','||' PO Element name'||','||' One time charge amount'||','||'
Onte time charge amount currency'||','||' Recurrent charge amount'||','||'
Recurrent charge amount currency'||','||' Personalized one time charge amount'||','||'
Personalized one time charge indicator'||','||' Personalized one time charge amount currency'||','||'
Personalized recurrent charge amount '||','||' Personalized charge indicator'||','||'
Personalized recurrent charge amount currency'||','||' Personalized recurrent charge period ' from dual;
spool off
--INSERT INTO NK_SHELL_EXECUTION_HISTORY(SCRIPT_NAME'||','||'LAST_EXECUTION_TIMESTAMP) VALUES ('NK_EXTRACT_CONTRACT_DATA.sh',TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'));
--COMMIT;
/
Shell Script File
#!/bin/sh
#export SCRIPT_PATH="/scripts/exeMRCReportFile.sh"
#sh ./scripts/exeMRCReportFile.sh
#echo "procedure execution completed"
echo Script Name: "$0"
echo Total Number of Argument Passed: "$#"
echo Arguments List -
echo 1. $1
if test -d $1;then
echo "Welcome $1"
else
echo "Not Exist";
fi
echo All Arguments are: "$*"
ORACLE_SID=RTXNP2
export ORACLE_SID
export PATH=.:$PATH:$ORACLE_HOME/bin
export REPORT_HOME=$BSCS_WORKDIR/Reports/ANT001A_REQ01
if [ $# -ne 0 ];then
export REPORT_GEN_PATH=$1
echo "$REPORT_GEN_PATH"
else
export REPORT_GEN_PATH=$WORK/Reports/ANT001A_REQ01/generated_reports
echo "$REPORT_GEN_PATH"
fi
##read -p 'Enter the database password for SYSADM user : ' password
echo "Please wait....The FTTH report is being created."
sqlplus SYSADM/SYSADM@$ORACLE_SID << eof_disp > $REPORT_HOME/logs/log_file_$$.log
@$REPORT_HOME/scripts/GEN_FTTH_REPORT_FILE.sql
eof_disp
echo "FTTH report created."
##echo "Sftp for for MRC report started"
##./util/sftpMRCReportFile.sh
##echo "Sftp for MRC report done"
