Hi Cero,
Thank you for your quick response.
I have tried the second option and used below in shell script.
set colsep ^
set heading off
set feedback off
set linesize 1000
set pagesize 0
set verify off
set trimspool on
My code:
#!/bin/bash
sqlplus -s arbor/arbor123@FXADMUAT << EOF> CN_DN.txt
set colsep ^
set feedback off
set linesize 1000
set pagesize 0
set verify off
set trimspool on
SELECT SourceIden,SourceFileName,GLAccountCode,Division,SubDivision,ProfitCentre1,ProfitCentre2,PlantCode,
tax_return_period ReturnPeriod,New_supp_id Supplie,Document_type DocumentType,supply_type SupplyType,
cr_db_no DocumentNumber,NVL(TO_CHAR(credit_note_dt,'YYYY-MM-DD'),TO_CHAR(debit_note_dt,'YYYY-MM-DD')) DocumentDate, old_
invoice_no
OriginalDocumentNumber,
TO_CHAR(old_invoice_date,'YYYY-MM-DD') OriginalDocmentDate, CRDRPr,line_item_no LineNumber,new_g_id_new
Customer,UINorComposition,OriginalCustom,
customer_name CustomerName,CustomerCode,BillToState,ShipToState,nvl(sns_pos,new_state_code) POS,PortCode,ShippingBillNum
ber,ShippingBillDate,FOB,Exportty,
new_HSN_CODE HSNorSAC,ProductCode,new_nature_of_supply ProductDescription,CategoryOfProduct,UnitOfMeasurement,Quantity,
cr_db_amount TaxableValue,IG_tax_rate IntegratedTaxRate,IG_tax_amount IntegratedTaxAmount, CGT_tax_RATE
CentralTaxRate,other_tax_rate,CGT_tax_AMOUNT CentralTaxAmount,other_tax_amount,SST_tax_RATE StateUTTaxRate,ST_tax_AMO
UNT StateUTTaxAmount,
CessRateAdvalorem,CessAmountAdvalorem,CessRateSpecific,CessAmountSpecific,
cr_db_amount+IG_tax_amount+CT_tax_AMOUNT+ST_tax_AMOUNT+other_tax_amount InvoiceValue,ReverseChargeFlag,TCSFlag,eCom
,
ITCFlag,ReasonForCreditDebitNote,AccountingVoucherNumber,AccountingVoucherDate,Userdefinedfield1,Userdefinedfield2,Userd
efinedfield3
FROM arbor.CN_DN_APR18_DATA2 WHERE UPPER(backout_flag)='VALID' AND new_gst_id_new IS NOT NULL;
exit
EOF
But got ouput as below with some extra spaces.
can you let me know how can i remove these space between field seperator(^).
I also want single header for all the rows data
KEN ^ ^ ^ ^Guj ^Outward ^GU
J ^POST ^042018 ^24AAAC ^CR ^TAX ^ 4030017721^2018-04-10
^ 3831006028^2018-01-03 ^ ^
1^24AAAC ^ ^ ^Mr Gaur
^ ^ ^ ^ 24^ ^ ^ ^
^ 9984^ ^Telecon Ser ^ ^ ^ ^
-13823.89^ 0^ 0^ 9^ 0^ -1244.15^ 0^ 9^ -1244.15^ ^
^ ^ ^ -16312.19^ ^ ^ ^ ^ ^
^ ^ ^