Shell script to execute sql query.

Hi Experts,

Need your support.
Not able to use sql query alias in shell script.

Could you please help me in finding right way to use alias with sql query in shell script.

Below is the code i am using.

#!/bin/bash
sqlplus -s abc/abc@abc << EOF> bcd.csv
set trimspool on
select zone_id AS 'ABC' ||','||language_code from ZONE_VALUES;
exit
EOF

Below is the error im getting :

select zone_id AS 'ABC'||','||language_code from ZONE_VALUES
                  *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

If i run the code without alias, code is executing fine.

Hi,
I see 2 problems with your query:
1) When you define an alias for a column either use double quotes to make it case sensitive or do not use quotes at all.
2) You try to concatenate 2 columns and somehow throw the alias definition into the middle of that concatenation. The result of the query without the alias definition is a single column, which can be aliased, but you cannot alias just a part of it.

I'll dare a wild guess about your final goal: you try to create a report with comma separated columns with headings. For this you either have to create the headings yourself or drop the concatenation.

Creating the headings is quite simple and usually preferred over the other method if columns do not have to have a fixed width:

#!/bin/bash
sqlplus -s abc/abc@abc << EOF> bcd.csv
set trimspool on heading off
select 'ABC,LANGUAGE_CODE' from dual;
select zone_id || ',' || language_code from ZONE_VALUES;
exit
EOF

The other way is to tell sqlplus how to format your report:

#!/bin/bash
sqlplus -s abc/abc@abc << EOF> bcd.csv
set trimspool on colsep ','
select zone_id as 'ABC' , language_code from ZONE_VALUES;
exit
EOF

Try both methods and use what fits your needs best.

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^ ^ ^               ^  ^                              ^
 ^         ^                                                                                                    ^

You'll have to use option 1 to get rid of the spaces (that is what I ment with fixed width columns).

Hi cero,

Could you please help as how can i perform addition in below code:

sqlplus -s arbor/arbor123@FXADMUAT << EOF> CN_DN1.txt
set trimspool on heading off
set linesize 30000
SET WRAP OFF
SELECT SourceIdentifier||'^'||SourceFileName||'^'||GLAccountCode||'^'||Division||'^'||SubDivision||'^'||ProfitCentre1||'
^'||ProfitCentre2||'^'||PlantCode||'^'||
tax_return_period||'^'||New_supplier_gst_id||'^'||Document_type||'^'||supply_type||'^'||cr_db_no||'^'||NVL(TO_CHAR(credi
t_note_dt,'YYYY-MM-DD'),TO_CHAR(debit_note_dt,'YYYY-MM-DD'))||'^'||old_invoice_no||'^'||TO_CHAR(old_invoice_date,'YYYY-M
M-DD')||'^'||CRDRPreGST||'^'||line_item_no||'^'||new_gst_id_new||'^'||UINorComposition||'^'||OriginalCustomerGSTIN||'^'|
|customer_name||'^'||CustomerCode||'^'||BillToState||'^'||ShipToState||'^'||nvl(sns_pos,new_state_code)||'^'||PortCode||
'^'||ShippingBillNumber||'^'||ShippingBillDate||'^'||FOB||'^'||ExportDuty||'^'||new_HSN_CODE||'^'||ProductCode||'^'||new
_nature_of_supply||'^'||CategoryOfProduct||'^'||UnitOfMeasurement||'^'||Quantity||'^'||cr_db_amount||'^'||IGST_tax_rate|
|'^'||IGST_tax_amount||'^'||CGT_tax_RATE||'^'||other_tax_rate||'^'||CGT_tax_AMOUNT||'^'||other_tax_amount||'^'||SGST_tax
_RATE||'^'||SGST_tax_AMOUNT||'^'||CessRateAdvalorem||'^'||CessAmountAdvalorem||'^'||CessRateSpecific||'^'||CessAmountSpe
cific||'^'||cr_db_amount+IGST_tax_amount+CGT_tax_AMOUNT+SGST_tax_AMOUNT+other_tax_amount||'^'||ReverseChargeFlag||'^'||T
CSFlag||'^'||eComGSTIN||'^'||
ITCFlag||'^'||ReasonForCreditDebitNote||'^'||AccountingVoucherNumber||'^'||AccountingVoucherDate||'^'||Userdefinedfield1
||'^'||Userdefinedfield2||'^'||Userdefinedfield3 FROM arbor.CN_DN_APR18_DATA2 WHERE UPPER(backout_flag)='VALID' AND new_
gst_id_new IS NOT NULL;
exit
EOF

As i have written/include below piece of query in my above code.

cr_db_amount+IGST_tax_amount+CGT_tax_AMOUNT+SGST_tax_AMOUNT+other_tax_amount

but it is throwing below error:

ERROR at line 3:
ORA-01722: invalid number

Please let me know how can i resolve this.