Shell script to execute sql query. Post: 303019567

Sponsored Content

Top Forums Shell Programming and Scripting Shell script to execute sql query. Post 303019567 by cero on Monday 2nd of July 2018 08:32:47 AM

07-02-2018

Registered User

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.

cero

View Public Profile for cero

Find all posts by cero

10 More Discussions You Might Find Interesting

1. 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. 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.

3. 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!

4. 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.

5. 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:

6. 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

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

8. 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...

9. 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

10. 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...

LEARN ABOUT HPUX

list_clearinghouse

list clearinghouse(1m)													    list clearinghouse(1m)

NAME

       list clearinghouse - Displays a list of all the clearinghouses whose names match the specified clearinghouse name

SYNOPSIS

       cdscp list clearinghouse clearinghouse-name [with attribute-name = attribute-value]

ARGUMENTS

       The full name of a specific clearinghouse.  The last simple name can contain wildcard characters.  The name of a particular attribute.  The
       value of a particular attribute.

DESCRIPTION

       The list clearinghouse command displays a list of all the clearinghouses whose names match the specified name.  The last  simple  name  can
       contain wildcards.  You can use a with attribute-name = attribute-value clause to limit output only to clearinghouses whose attributes have
       values equal to the specified values.  A space must precede and follow the = (equals sign).

   Privilege Required
       You must have read permission to the directory that stores the associated clearinghouse object entry.  If you use a with  attribute-name  =
       attribute-value clause in the command, you also need read or test permission to the selected clearinghouses.

NOTE

       This command is replaced at Revision 1.1 by the dcecp command and may not be provided in future releases of DCE.

EXAMPLE

       The following command displays a list of all the clearinghouses named in the root directory: cdscp> list clearinghouse /.:/*

			       LIST
		      CLEARINGHOUSE   /.../abc.com/*
				 AT   1991-10-15-15:56:00 /.../abc.com/Munich_CH /.../abc.com/Paris_CH

RELATED INFORMATION

       Commands:  clear  clearinghouse(1m),  create clearinghouse(1m), delete clearinghouse(1m), set cdscp preferred clearinghouse(1m), show cdscp
       preferred clearinghouse(1m), show clearinghouse(1m)

															    list clearinghouse(1m)

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell Script to execute SQL's

Discussion started by: osramos

2. Shell Programming and Scripting

TO execute .sql 2005 query file in shell script

Discussion started by: n2ekhil

3. Shell Programming and Scripting

query sql using shell script

Discussion started by: kingpeejay

4. Shell Programming and Scripting

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

Discussion started by: sandeep909