SQL query in a loop with single sqlplus connection

Shell Programming and Scripting
1

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:

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

2

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 = .

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

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

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

You could consider to use the SQL loader to load the data in a table.

5

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 -

Account "ACC_1"
Account "ACC_2"
Account "ACC_3"

you could process it thereby creating a script like so -

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.

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