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 :

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

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

1 Like

This needs bash:

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

or a little more secure...

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.

1 Like

It might be easier with a Here Document:-

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