Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Storing multiple sql queries output into variable by running sql command only once

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Storing multiple sql queries output into variable by running sql command only once
# 1  
Old 05-25-2016
Lightbulb Storing multiple sql queries output into variable by running sql command only once
Hi All,


I want to run multiple sql queries and store the data in variable but i want to use sql command only once. Is there a way without running sql command twice and storing.Please advise.

Eg :

Code:
Select 'Query 1 output' from dual; 

Select 'Query 2 output' from dual;

I want to store output for above query by running sql command script only once




Moderator's Comments:
Mod Comment Please use code tags as required by forum rules!
Last edited by RudiC; 05-25-2016 at 07:37 AM.. Reason: Added code tags
# 2  
Old 05-26-2016
You can do this in a few ways.
  • You can call sqlplus and pass it a deck/file of multiple SQL commands.
  • You can call sqlplus in what is called a here document and pass in the commands from your script
  • Probably others......

How much SQL code are you planning to pass in, and will it be static or based on variables in the main script?




Robin
This User Gave Thanks to rbatte1 For This Post:
Rokkesh
# 3  
Old 05-27-2016
This needs bash:

Code:
sql_output="$(echo "select yourfield from yourtable;" \
      | mysql -uusername -p"password" database)"

or a little more secure...

Code:
sql_output="$(echo "select yourfield from yourtable;" \
      | mysql --defaults-extra-file=$HOME/.my.cnf_with_password -uusername database)"

mysql option "--skip-column-names" may be relevant too.
Last edited by stomp; 05-27-2016 at 10:01 AM..
This User Gave Thanks to stomp For This Post:
Rokkesh
# 4  
Old 06-04-2016
It might be easier with a Here Document:-

Code:
sql_output=`sqlplus -S <<-EOSQL
$my_db_userid
$my_db_password
select 'query 1' from dual ;
select 'query 2' from dual ;
EOSQL`

I've not checked this, but it is pretty close, I think. The commands to be input to sqlplus as though it was an interactive session are between the two EOSQL markers.



Robin



RObin
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 storing the output of multiple sql queries to a file

Hi All, I have a file queries.txt as follows : SELECT COLUMN1 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN1 FROM SCDEMA2.TABLE2; SELECT COLUMN2 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN2 FROM SCDEMA2.TABLE2; SELECT COLUMN3 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN3 FROM SCDEMA2.TABLE2; SELECT... (2 Replies)
Discussion started by: SriRamKrish
2 Replies

2. Shell Programming and Scripting

Create Multiple UNIX Files for Multiple SQL Rows output

Dear All, I am trying to write a Unix Script which fires a sql query. The output of the sql query gives multiple rows. Each row should be saved in a separate Unix File. The number of rows of sql output can be variable. I am able save all the rows in one file but in separate files. Any... (14 Replies)
Discussion started by: Rahul_Bhasin
14 Replies

3. Shell Programming and Scripting

How to store results of multiple sql queries in shell variables in ksh?

Hi, I have a script where I make a sqlplus connection. In the script I have multiple sql queries within that sqlplus connection. I want the result of the queries to be stored in shell variables declared earlier. I dont want to use procedures. Is there anyway else. Thanks in advance.. Cheers (6 Replies)
Discussion started by: gonchusirsa
6 Replies

4. Shell Programming and Scripting

Execute multiple SQL scripts from single SQL Plus connection

Hi! I would like to do a single connection to sqlplus and execute some querys. Actually I do for every query one connection to database i.e echo 'select STATUS from v$instance; exit' > $SQL_FILE sqlplus user/pass@sid @$SQL_FILE > $SELECT_RESULT echo 'select VERSION from v$instance;... (6 Replies)
Discussion started by: guif
6 Replies

5. Shell Programming and Scripting

Running multiple sql files in unix

In c:/zaheer/123-456/sql/ folder i have below 7 sql files. cv_abc_a.sql cv_abc_b.sql cv_abc_c.sql vtemp_d.sql vtemp_e.sql cv_abc_f.sql Exviews.sql In Exviews.sql file i have mentioned all cv and vtemp files to run like start ${SQLDIR}/cv_abc_a.sql start ${SQLDIR}/cv_abc_b.sql... (1 Reply)
Discussion started by: Zaheer.mic
1 Replies

6. Shell Programming and Scripting

Help! Paste Multiple SQL output result to exec command

Hi, I want to write the shell script to change multple file name (the file name is get from DB) e.g. cp db1.txt file1_new.txt cp db2.txt file2_new.txt cp db3.txt file3_new.txt I have write the script like this: VAR=`sqlplus -s $LOGON @<<ENDOFTEXT set termout off ... (0 Replies)
Discussion started by: jackyntk
0 Replies

7. Shell Programming and Scripting

sql output into a variable

i have to do a check in my UNIX script to see whats saved in the database. depending on whats there will fork data to certain functions. However i do not know how to capture SQL output into a UNIX variable. Below is what i have tried, but i get an error: Error 3706 Failure 3706 Syntax error:... (3 Replies)
Discussion started by: purplebirky
3 Replies

8. Shell Programming and Scripting

Sending SQL Queries output to different Excel sheets

Hi, I need your help in sedning sql queries output to different excel sheets. My requirement is like this: Query1: Select name from table1 where status = 'Complete' Query2: Select name from table1 where status = 'Failed' Query3: Select name from table1 where status = 'Ignored' ... (4 Replies)
Discussion started by: parvathi_rd
4 Replies

9. Shell Programming and Scripting

Problem while storing sql query value in a variable

Hi, When i execute the below statement , the value is not getting stored in the variable. AnneeExercice=`sqlplus $LOGSQL/$PASSWORDSQL << FIN >> $GEMOLOG/gemo_reprev_reel_data_ventil_$filiale.trc SELECT bi09exercice FROM bi09_scenario WHERE bi09idfiliale=UPPER('de') AND ... (1 Reply)
Discussion started by: krishna_gnv
1 Replies

10. Shell Programming and Scripting

Run SQL queries in DB2 and output to file

Hi, I new to Unix and scripting. Following is my requirement. Can someone tell me whether its possible or not. Also please let me know how to proceed further if this is possible. List of queries are stored in a file. For example, I have to run a query like this: Select * from &XYZ where... (0 Replies)
Discussion started by: simhasuri
0 Replies
Login or Register to Ask a Question