Shell script to execute sql query.


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Shell script to execute sql query.
# 1  
Old 07-02-2018
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.

Code:
#!/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 :

Code:
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.
# 2  
Old 07-02-2018
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:
Code:
#!/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:
Code:
#!/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.
# 3  
Old 07-03-2018
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:

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

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


Last edited by as7951; 07-03-2018 at 02:50 AM..
# 4  
Old 07-03-2018
You'll have to use option 1 to get rid of the spaces (that is what I ment with fixed width columns).
# 5  
Old 07-03-2018
Hi cero,

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

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.
Code:
cr_db_amount+IGST_tax_amount+CGT_tax_AMOUNT+SGST_tax_AMOUNT+other_tax_amount

but it is throwing below error:

Code:
ERROR at line 3:
ORA-01722: invalid number

Please let me know how can i resolve this.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Help with shell script to execute a sql cursor

Hi All, I want some help in writing a shell script which will be in the below form: A logical representation of shell scripts: ->Shell scripts start ->Connect to database ->Execute a sql and store the output in a cursor(need to know is there any concept of cursor in unix) ->Using a loop... (2 Replies)
Discussion started by: Chitragupta
2 Replies

2. Shell Programming and Scripting

How to execute a sql querry from shell script?

hello forum , I am new to shell scripting. as part of my assignment i have to execute a sql querry from the shell script. please help me. example. sql queerry:db2 "select emp_id from tsble emp". i have to execute the above querry from the script. advance thanks to forum Siva ranganath (1 Reply)
Discussion started by: workforsiva
1 Replies

3. Red Hat

Sql query through shell script

hey , i am using this code to store value of a sql query and and then use it in other query but after some time , but it is not working. please help #!/bin/bash val_1=$( sqlplus -s rte/rted2@rel76d2 << EOF setting heading off select max(stat_id) from cvt_stats; exit EOF ) nohup... (5 Replies)
Discussion started by: ramsavi
5 Replies

4. Shell Programming and Scripting

Shell Script to execute Oracle query taking input from a file to form query

Hi, I need to query Oracle database for 100 users. I have these 100 users in a file. I need a shell script which would read this User file (one user at a time) & query database. For instance: USER CITY --------- ---------- A CITY_A B CITY_B C ... (2 Replies)
Discussion started by: DevendraG
2 Replies

5. Shell Programming and Scripting

Execute SQL query in unix script

Hi I am new in unix. oracle and unix are installed in my sytem.i need the script which could connect to the oracle using username ,password and schema and can run the select * from tab query. Thanks vijay (8 Replies)
Discussion started by: vijays3
8 Replies

6. Programming

how to execute the sql quires from shell script?

hi all, I'm new to shell scripting, i want to know how to use the sql statements from the shell script? is there any documents or pdf to learn from the begining? plese provide me some useful links on the same. Thanks all in advance..,:b: (3 Replies)
Discussion started by: vij_krr
3 Replies

7. Shell Programming and Scripting

how to execute shell script in another server that contain sql.

hi all, how to execute shell script in another server that contain sql script. i tried using "ssh -l" option.. but i am unable to load the environment variable of the remote host. please suggest how execute the shell script contain sql part inside it. (2 Replies)
Discussion started by: sandeep909
2 Replies

8. Shell Programming and Scripting

query sql using shell script

query sql using shell script, is it possible? my friend told me to do a file.sql and link to my shell script, but can i query sql using shell script? thanks in advance! (2 Replies)
Discussion started by: kingpeejay
2 Replies

9. Shell Programming and Scripting

TO execute .sql 2005 query file in shell script

Hi, I know in oracle a .sql file is called by @ <path> /<filename>. But how to call in sql 2005, I am opening the sql sessionwith sqsh, is there any command to execute there a .sql file (query in sql 2005) in K shell script. (0 Replies)
Discussion started by: n2ekhil
0 Replies

10. Shell Programming and Scripting

Shell Script to execute SQL's

Hi people, i need youre help on this. I need to create an Shell SCript to list in a directory some sql files and then execute them one by one . Also i need to have Logs from the executed Sql's to send to our costummers. Can anyone help me on this Regards, osramos (2 Replies)
Discussion started by: osramos
2 Replies
Login or Register to Ask a Question