Storing the SQL results in array variables

Shell Programming and Scripting
1

Requirement 1) I need to execute 15 SQL queries in oracle through linux script. All these query results needs to be stored in array variables.

Requirement 2) And these 15 queries needs to be executed in parallel.

Requirement 3) Once all the queries executed then the shell script should exit by echoing all the results of these queries.

All the 15 queries are passed though parameter file in linux.

Please let me know how this can be achieved.

2

We're not Unix consultants that work to solve user requirements.

Here's a start using heredoc:

output=$(sqlplus <...> <<EOF
start $1.sql
quit
EOF
)
echo $output

Unfortunately that's a pretty vague answer, but it would be easier if you posted specific questions and not the whole requirement.

3

thank you for your comments. I have got what i expected.

4 
#!/bin/ksh
#*****************ORACLE CALL FUNCTION****************
function run_oracle {
sqlplus -s <<%%
${user}/${passwd}
set serveroutput off
set heading off
set feedback off
set verify off
set define off
set linesize 2000
${cmd};
exit
%%
}
#***********ORACLE CALL FUNCTION ENDS*************
 
#**********PROCESS TO RUN MULTIPLE QUERIES IN PARALLEL USING "&" IN ORACLE FUNCTION*****************
set -A result ### Declaring the Array
set -A Query ### Declaring the Array
i=0
echo "Dear all, Checks are :- ">${mailfile}
echo "" >> ${mailfile}
start_date=`date +'%H:%M:%S'`
while IFS=\| read chk_nbr chk_name chk_query ## Reading queries and check from parameter file ${chk_file} #######
do
echo ${chk_nbr}
cmd="${chk_query}"
Query[${i}]=${chk_name}
echo ${chk_query}
if [ ${chk_nbr} == "Y" ]; then
result[${i}]=`run_oracle | grep -v '^$' | tail -1` &
process[${i}]=`echo $! | head -1`
echo ${process[${i}]}
let i=i+1
fi
done < ${chk_file}
#***********************PROCESS TO RUN MULTIPLE QUERIES IN PARALLEL USING "&" IN ORACLE FUNCTION ENDS*****************
 
echo `echo ${result[0]}|sed 's/[ ]*//g'` 
 
cnt=`expr $i - 1`
n=0
while [ $n -le $cnt ]
do
p=${process[$n]} #### FETCHES ALL THE PROCESS IDS WHICH ARE TRIGGERED IN ORACLE BY ABOVE PROCESS #########
 
c=`ps -A | grep ${p} | tr ' ' '|' | awk -F'|' -v var=${p} '$1==var'|wc -l` #### CHECKS PROCESS IDS ARE LIVE WHICH ARE TRIGGERED ######
if [ $c -eq 0 ]; then 
echo " ${result[${n}]} : ${Query[$n]}"
let n=n+1
fi
done
n=`expr $n - 1`
if [ $n -eq $cnt ]; then
echo "completed successfully!!"
return 0
else
echo "failed.Please run again!"
return 1
fi

---------- Post updated at 01:21 PM ---------- Previous update was at 01:07 PM ----------

cat ${chk_file}
 

Y|Duplicate in Table1 |select count(count(*)) from TABLE1  group by COL1,COL2 having count(*)>1

Y|Duplicate in Table2 |select count(count(*)) from TABLE2 group by  COL1,COL2 having count(*)>1

This parameter file will be used by above script to trigger this queries. While i am executing the query output is not being assigned to Array variables. Please help me with this..