Shell script to fetch sql query data in csv file

Question

Need to extract the below query data along with header in csv file using shell script.

Below is the query.

SELECT SourceIdentifier,SourceFileName,ProfitCentre2,PlantCode,
tax_retur ReturnPeriod,document_number DocumentNumber,TO_CHAR(invoice_generation_date,'YYYY-MM-DD')
Docume,Original,customer_name CustomerName,NVL(sns_pos,new_state_code)POS,PortCode,NEW_HSN_CODE HSNorSAC,(SGSATE+UTGSATE) Stat,(SGS+UT)StateUT,Userde FROM arbor.INV_REPO_FINA WHERE UPPER(document_type)='INV' AND UPPER(backout_flag)='VALID' AND new_gst_id_new IS NOT NULL AND new_charges<>0 AND taxable_adj=0
UNION
SELECT SourceIdentifier,SourceFileName,ProfitCentre2,PlantCode,
tax_retur ReturnPeriod,document_number DocumentNumber,TO_CHAR(invoice_generation_date,'YYYY-MM-DD')
Docume,Original,customer_name CustomerName,NVL(sns_pos,new_state_code)POS,PortCode, NEW_HSN_CODE HSNorSAC,(SGSATE+UTGSATE) Stat,(SGS+UTG)StateUT,Userde FROM arbor.INV_REPO_FINA WHERE UPPER(document_type)='INV' AND UPPER(backout_flag)='VALID' AND new_gst_id_new IS NOT NULL AND new_charges<>0 AND  taxable_adj<>0

Could please let me know if below approach to fetch data using shell script is correct and script is correct.

#!/bin/bash
file="output.csv"
sqlplus -s username/password@Oracle_SID << EOF
SPOOL $file

select 'SourceIdentifier','SourceFileName','ProfitCentre2','PlantCode',
'tax_retur ReturnPeriod','document_number DocumentNumber','TO_CHAR(invoice_generation_date,'YYYY-MM-DD') Docume','Original','customer_name CustomerName','NVL(sns_pos,new_state_code)POS','PortCode','NEW_HSN_CODE HSNorSAC','(SGSATE+UTGSATE) Stat','(SGS+UT)StateUT','Userde' from dual
Union all
select 'TO_CHAR(SourceIdentifier)','TO_CHAR(SourceFileName)','TO_CHAR(ProfitCentre2)','TO_CHAR(PlantCode)',
'TO_CHAR(tax_retur ReturnPeriod)','TO_CHAR(document_number DocumentNumber)','TO_CHAR(invoice_generation_date,'YYYY-MM-DD')
Docume','TO_CHAR(Original)','TO_CHAR(customer_name CustomerName)','TO_CHAR(NVL(sns_pos,new_state_code)POS)','TO_CHAR(PortCode)','TO_CHAR(NEW_HSN_CODE HSNorSAC)','TO_CHAR((SGSATE+UTGSATE) Stat)','TO_CHAR((SGS+UT)StateUT)','TO_CHAR(Userde)' from
(SELECT SourceIdentifier,SourceFileName,ProfitCentre2,PlantCode,
tax_retur ReturnPeriod,document_number DocumentNumber,TO_CHAR(invoice_generation_date,'YYYY-MM-DD')
Docume,Original,customer_name CustomerName,NVL(sns_pos,new_state_code)POS,PortCode,NEW_HSN_CODE HSNorSAC,(SGSATE+UTGSATE) Stat,(SGS+UT)StateUT,Userde FROM arbor.INV_REPO_FINA WHERE UPPER(document_type)='INV' AND UPPER(backout_flag)='VALID' AND new_gst_id_new IS NOT NULL AND new_charges<>0 AND taxable_adj=0
UNION
SELECT SourceIdentifier,SourceFileName,ProfitCentre2,PlantCode,
tax_retur ReturnPeriod,document_number DocumentNumber,TO_CHAR(invoice_generation_date,'YYYY-MM-DD')
Docume,Original,customer_name CustomerName,NVL(sns_pos,new_state_code)POS,PortCode, NEW_HSN_CODE HSNorSAC,(SGSATE+UTGSATE) Stat,(SGS+UTG)StateUT,Userde FROM arbor.INV_REPO_FINA WHERE UPPER(document_type)='INV' AND UPPER(backout_flag)='VALID' AND new_gst_id_new IS NOT NULL AND new_charges<>0 AND  taxable_adj<>0)

SPOOL OFF
EXIT
EOF

is your script working ? if not what error you are getting ? — ntshetty
– ntshetty, Commented Apr 12, 2018 at 7:44
@ThiruShetty...its not working and also no giving any error and output — as7951
– as7951, Commented Apr 12, 2018 at 8:25

torbatamas · Accepted Answer · 2018-04-12 08:57:15Z

1

In short: the ; is missing from the end of the select statement.

Some unrequested advice:

I think spool will put extra stuff into your file (at least some new lines), a redirect is better, further the first line is not db-related:

echo "SourceIdentifier;SourceFileName;ProfitCentre2..." > $file

I recommend to generate the csv format right in the select query, later it will be more headache (you can escape there what you want):

$query = "select SourceIdentifier || ';' || SourceFileName || ';' || ProfitCentre2 ... ;"

So querying the DB (I think capital -S is the right one) plus for the formatting of the records (and maybe you want to format your columns too):

sqlplus -S username/password@Oracle_SID >> $file << EOF
set linesize 32767 pagesize 0 heading off
$query
EOF

answered Apr 12, 2018 at 8:57

torbatamas

1,3461 gold badge12 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

as7951 Over a year ago

Thank you.. Will execute and let you know for errors in case any

Mantu · Accepted Answer · 2020-08-04 08:18:26Z

0

For me this one is working but one empty line before first query and second query is coming. Empty line remove using awk command

#!/bin/bash

FILE="A.csv"
$ORACLE_HOME/bin/sqlplus -s username/password@Oracle_SID<<EOF
SET PAGESIZE 50000 COLSEP "," LINESIZE 20000 FEEDBACK OFF HEADING off
SPOOL $FILE

select 'TYPE_OF_CALL_V','SWITCH_CALL_TYPE_V','RECORD_TYPE_V','TARF_TYPE_V' from dual;
SELECT TYPE_OF_CALL_V,SWITCH_CALL_TYPE_V,RECORD_TYPE_V,TARF_TYPE_V FROM TABLE;

SPOOL OFF
EXIT
EOF

awk 'NF > 0' $FILE  > out.txt
mv out.txt $FILE

edited Aug 4, 2020 at 8:18

answered Aug 4, 2020 at 8:01

Mantu

1491 silver badge5 bronze badges

Collectives™ on Stack Overflow

Shell script to fetch sql query data in csv file

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related