Connecting once using sqlplus and doing multiple queries

UNIX for Advanced & Expert Users
1

Hello everyone,

It's my first week using unix and shell scripting. I tried creating a script that has a function that execute SQL query. my script looks something like this:

----------------------------------------------------

#!/bin/sh
 
tableName="myTable"
secondTable="secondTable"
 
myFunction()
{
myQuery=`sqlplus -s usr/pass << EOF
set heading off
SELECT COUNT(*) FROM $tableName WHERE <condition>;
EOF`
if [ $myQuery = '0' ]; then
myQuery=`sqlplus -s usr/pass << EOF
set heading off
SELECT COUNT(*) FROM $secondTable WHERE <condition>;
EOF`
fi
return $myQuery
}
 
myFunction
output=`echo $?`

----------------------------------------------------

Correct me if I'm wrong, but I believe that if I always use the "sqlplus -s usr/pass" statement everytime I need a record query, It would slow down my processing.. I have to call this function about 5000 times (need to query 5000 times to check record).

how can I execute multiple queries (select) without being redundant on the ''sqlplus -s....'' statement?

Please help me. Thank you.

2

If you need to do that 5000 times, you should use PL/SQL. Otherwise if you are using ksh or bash 4, you can use one sqlplus as a co-process.

4

Hi Sir,

Can you help me on how to convert it to PL/SQL? I'm running this script on sh (bourne shell).

I have no idea on how to process multiple queries using PL/SQL.

Thank you Sir.