SQL query in a loop with single sqlplus connection


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting SQL query in a loop with single sqlplus connection
# 1  
Old 01-31-2011
SQL query in a loop with single sqlplus connection

Hi,

I'm trying to build a shell script that reads a set of accounts from a file. For each account I need to perform a set of sql queries. So I have a loop with a set of sqlplus connections to retrieved my data.

Is it possible to have a single sqlplus connection before entering the loop and use this connection to the database inside the loop to avoid having to connect every time I need to perform an sql statement?

Currently, the related piece of code I have, is similar to this one:
Code:
for j in `cat $INPUT_FILE | grep Account`
do
  echo $j  
  account_no=`echo $j|cut -d'"' -s -f2`
  echo $account_no
  row_out=`sqlplus -S $LOGIN << BEOF |grep -v '^$'|grep -v selected
  set heading off  
  set pages 0
  SELECT address
  FROM  account_table
  WHERE account_no = '$account_no'
BEOF
`
    echo $row_out >> $INPUT_FILE.out
done

Many thanks

Last edited by Franklin52; 01-31-2011 at 04:42 AM.. Reason: Please use code tags
# 2  
Old 01-31-2011
One way would be, store all the grep'd Account into a variable say account_no and do some simple edit to make the values inside a comma separated one. Then use the select statement's IN clause instead of = .
Code:
row_out=`sqlplus -S $LOGIN << BEOF |grep -v '^$'|grep -v selected 
  set heading off  
  set pages 0
  SELECT address
  FROM  account_table
  WHERE account_no IN ( $account_no )
BEOF
`

If you do not want to store the output in a variable (row_out) then you can also try like..
Code:
sqlplus -S $LOGIN << BEOF |grep -v '^$'|grep -v selected > ${INPUT_FILE}.out
  set heading off  
  set pages 0
  SELECT address
  FROM  account_table
  WHERE account_no IN ($account_no)
BEOF

# 3  
Old 01-31-2011
Thanks Michael,

The problem is that the number of accounts might be really large (several thousands) and for each one I need to performs several actions based on the results returned in the query (part of the logic in the loop). My main concern with having to connect every time I perform a sql query is performance. That's why I wanted to avoid all these connections.

But, in any case, thanks again for your response.
# 4  
Old 01-31-2011
You could consider to use the SQL loader to load the data in a table.
# 5  
Old 02-01-2011
Alternatively, if you do not want to/cannot store all account numbers in a variable, then you could create an Oracle SQL script from your data file. So if your data file looks like this -

Code:
Account "ACC_1"
Account "ACC_2"
Account "ACC_3"

you could process it thereby creating a script like so -

Code:
SELECT address
FROM  account_table
WHERE account_no IN (
'ACC_1',
'ACC_2',
'ACC_3'
);

This script could then be fed to a single sqlplus session.

Yet another powerful alternative in Oracle is - external tables.

Quote:
...
The problem is that the number of accounts might be really large (several thousands) and for each one I need to performs several actions based on the results returned in the query (part of the logic in the loop). ...
If those "actions" are database actions, then of course, you'll need a more complex Oracle script. Otherwise if they are *nix actions, then you have your address list at your disposal.
Really depends on what you are trying to do next.

HTH,
tyler_durden
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to use for loop to execute multiple .sql files while using SQLPLUS for db connection.?

Hello , Im calling every single file inside my script like 1.sql,2.sql so on it looks so tedious. I want to replace with for loop where every file gets executed. When i use for loop im getting errorUnexpected EOF] , can anyone please help me out in this.. How i can use for loop to invoke my... (6 Replies)
Discussion started by: preethi87
6 Replies

2. UNIX for Dummies Questions & Answers

multiple queries in single sqlplus

I need to connect a databas eusing sqlplus and i need to store the results of each query in separate file. is it possible to achieve this usng single sqlplus? i dont want to connect each time for each query. thanks (3 Replies)
Discussion started by: pandeesh
3 Replies

3. Shell Programming and Scripting

How to stop Sqlplus command from printing db connection details

Hi, Could someone tell me how to stop SQLPLUS command from printing the connection details in the console. Below is the lines i get in console when executing the sqlplus... SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 9 03:31:03 2011 Copyright (c) 1982, 2005, Oracle. All rights... (2 Replies)
Discussion started by: funonnet
2 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

SQLPLUS query in Unix script

Hi, I am using sqlplus query to get results in a csv format in unix. I am using ksh, and below is the query. echo "select r.num|| ',' || p.path ||',"' || r.issue_description ||'",' ||p.timestamp from events r, messagepath p;">> $QUERY_FILE sqlplus -s $LOGIN @ $QUERY_FILE>>$OUTFILE ... (2 Replies)
Discussion started by: Nutan
2 Replies

6. Programming

Single sql query to spool to multiple files

Is there anyway to spool my select statement into spool files of max 10000 records each? eg I have a select statement that will return 45000 records. A normal spool command will output the 45000 into just one spool file. How can I make sqlplus do this? 00001 - 10000 records --- spool... (3 Replies)
Discussion started by: Leion
3 Replies

7. UNIX for Advanced & Expert Users

Set shell variables from SQLPLUS query results

Hi All, I needed to get the result of two sqlplus queris into shell variables. After days of looking for the ultimate solution to this problem.. i found this... sqlplus -s USER/PASS@DB <<EOF | awk '{if(NR==1) printf("%s ", $1); if(NR==2) printf("%s ", $1);}' | read VAR1 VAR2 set head off... (2 Replies)
Discussion started by: pranavagarwal
2 Replies

8. Shell Programming and Scripting

Executing pl/sql using sqlplus on unix

Hi to all, I have a endday.sh file. And I execute this like "sh endday.sh" from command prompt. In endday.sh file it writes: sqlplus temp/temp@data @run.sql& echo $!>>pid.txt However my aim is not to put the pid into pid.txt but I need to insert the pid into an oracle table using sqlplus.... (1 Reply)
Discussion started by: maverick1234
1 Replies

9. UNIX for Advanced & Expert Users

Executing pl/sql using sqlplus on unix

Hi to all, I have a endday.sh file. And I execute this like "sh endday.sh" from command prompt. In endday.sh file it writes: sqlplus temp/temp@data @run.sql& echo $!>>pid.txt However my aim is not to put the pid into pid.txt but I need to insert the pid into an oracle table using sqlplus.... (1 Reply)
Discussion started by: maverick1234
1 Replies

10. Programming

Executing pl/sql using sqlplus on unix

Hi to all, I have a endday.sh file. And I execute this like "sh endday.sh" from command prompt. In endday.sh file it writes: sqlplus temp/temp@data @run.sql& echo $!>>pid.txt However my aim is not to put the pid into pid.txt but I need to insert the pid into an oracle table using sqlplus.... (1 Reply)
Discussion started by: maverick1234
1 Replies
Login or Register to Ask a Question