Shell Script to execute Oracle query taking input from a file to form query

Shell Programming and Scripting
1

Hi,

I need to query Oracle database for 100 users. I have these 100 users in a file. I need a shell script which would read this User file (one user at a time) & query database.

For instance:

USER     CITY
---------  ----------
A             CITY_A
B             CITY_B
C             CITY_C
D             CITY_D
E             CITY_E
F             CITY_F

sqlplus -S -L $Usernam/$Password@$SID
while read USER
do
select CITY from TableA where USER='$USER';
done
exit
2

It would be quicker to load all these users into a temp table and join it against whatever table you want.

3

Depending on how much users you have, then Shell_Life's solution will be better, but here is one:

#!/bin/ksh
############################################################################
buildDBSearchStr ()
{
	inFile="${1}"
	
	if [ ! -f "${inFile}" -o ! -r "${inFile}" ]
	then
		echo "Input file: [${inFile}] does not exist or is not readable."
		exit 1
	fi
	
	numUsers=0
	unset dbSearchStr
	
	while read dbUserName
	do
		dbUserName="${dbUserName:-NULL}"
		
		[ "${dbUserName}" == "NULL" ] && continue
		
		if [ ${numUsers} -eq 0 ]
		then
			dbSearchStr="'${dbUserName}'"
		else
			dbSearchStr="${dbSearchStr},'${dbUserName}'"
		fi
		numUsers=`expr ${numUsers} + 1`
	done < "${inFile}"

	if [ ${numUsers} -eq 0 ]
	then
		echo "No users added."
		exit 1
	fi
	
	echo "DB Search string: [${dbSearchStr}]"
}
############################################################################

############################################################################
# Main
############################################################################
if [ $# -ne 1 ]
then
	echo "Arguments required: [1] - [Users Input File]."
	exit 1
fi

dbOutputFile="./dbUsersData.txt"
dbLogFile="./dbUsersData.log"

buildDBSearchStr "${1}"

searchDBQuery="SELECT CITY FROM TableA WHERE USER IN (${dbSearchStr});"
dbSets="SET LINESIZE 600 PAGESIZE 0"
echo "${dbSets}\n${searchDBQuery}\nEXIT;" | sqlplus -S -L ${DBUSER}/${DBPASS}@${ORACLE_SID} 1> "${dbOutputFile}" 2> "${dbLogFile}"
retCode=${?}

if [ ${retCode} -ne 0 ]
then
	echo "Failed to execute query: [${retCode}]. Check log file: [${dbLogFile}]."
else
	echo "Query executed successfully!"
fi

############################################################################

The input file must be of form, otherwise you can change the code to suit your needs:

# cat searchUsers.txt
A
B
C
D
E
F

And must execute it as:

./searchUsersDB.sh searchUsers.txt

It is quite simple, but I hope it helps! :wink: