Storing the SQL results in array variables


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Storing the SQL results in array variables
# 1  
Old 08-14-2013
Code Storing the SQL results in array variables

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.

Last edited by Niranjancse; 08-14-2013 at 12:55 PM.. Reason: Edited the title
# 2  
Old 08-14-2013
We're not Unix consultants that work to solve user requirements.

Here's a start using heredoc:
Code:
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  
Old 08-14-2013
thank you for your comments. I have got what i expected.
# 4  
Old 08-16-2013
Code

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

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

Last edited by Franklin52; 08-16-2013 at 04:58 AM.. Reason: Please use code tags
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

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... (3 Replies)
Discussion started by: Rokkesh
3 Replies

2. Shell Programming and Scripting

How to Assign SQL Query Results to Variables in Linux?

Hi, I am new to linux... How to Assign SQL Query Results to Variables in Linux,i want ti generate it in param files, Can anyone please explain me. Ex: SQL> Select * from EMP; O/P: Emp_No Emp_Name 1 AAA 2 BBB 3 CCC and I want expected... (5 Replies)
Discussion started by: Sravana Kumar
5 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

SQL/Plus in a coprocess example. Also saves query results into shell variables

While assisting a forum member, I recommended running SQL/Plus in a coprocess (to make database connections and run a test script) for the duration of his script rather than starting/stopping it once for every row in a file he was processing. I recalled I made a coprocess example for folks at... (2 Replies)
Discussion started by: gary_w
2 Replies

5. Shell Programming and Scripting

Storing outputs into variables

I need to know how to store output from one command so that it can initiate another command. chktraf -s | cut -c1-4 output would look like 321 142 256 342 123 Then if the value of the output = 0, then initiate next command. if then for xx in 01 02 03 04 05 06 07 08 09 10 do ... (4 Replies)
Discussion started by: Shaun74
4 Replies

6. UNIX for Dummies Questions & Answers

Storing variables and using them..

Hello Apologies for not having the most accurate of thread titles.. I'm using IBM Rational Synergy CM software. I use the Synergy commands in tandem with Unix commands. I have a directory containing source code objects: bash-3.00$ ccm ls *.fmb *.rdf *.pll *.mmb cre_applications.fmb-1... (1 Reply)
Discussion started by: Glyn_Mo
1 Replies

7. Shell Programming and Scripting

perl: storing regex in array variables trouble

hi this is an example of code: use strict; use warnings; open FILE, "/tmp/result_2"; my $regex="\\ Starting program ver. (.*)"; my $res="Program started, version <$1> - OK.\n"; while (<FILE>) { if ($_ =~ /($regex)/) { print "$res"; } } close FILE; This finds $regex and print... (3 Replies)
Discussion started by: xist
3 Replies

8. Shell Programming and Scripting

Storing commands in $variables.

Hi I'm trying to store commands in variables... like so.. # lastcmd=" $t1 | $t2 | $t3 | $t4 | sort | uniq" t1="sed -e 's/http:/<li><a href=\"http:/'" t2="sed -e 's/http:.*/&\">&<\/a>Web Link<br>/'" t3="sed -e 's/.*. mailto:/<li><a href=\"mailto:/'" t4="sed -e... (7 Replies)
Discussion started by: Paulw0t
7 Replies

9. Shell Programming and Scripting

storing variables in array.Please help

Hi All, I need some help with arrays. I need to take input from the user for hostname, username and password until he enters .(dot) or any other character and store the values in the variable array. I would further connect to the hostname using username and passwd and copy files from server to... (7 Replies)
Discussion started by: nua7
7 Replies

10. 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
Login or Register to Ask a Question