CODEWITHSUNDEEP
linuxbashshellscripting
as7951
as7951

Reputation: 187

Shell script to fetch sql query data in csv file

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

Upvotes: 0

Views: 11339

Answers (2)

Mantu
Mantu

Reputation: 149

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

Upvotes: 0

torbatamas
torbatamas

Reputation: 1296

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

Upvotes: 1

Related Questions