Shell Programming and Scripting

$ cat testScript.sh
execSQLPlus ()
{
	deptNo="${1}"
	sqlplus -S -L /nolog <<EOF
		whenever sqlerror exit 1
		connect user@database/password
		set serveroutput on size 1000000
		variable deptno number;
		exec :deptno := '${deptNo}'
		select customer_name from customer where customer_id=:deptno;
EOF
	sqlplusRetCode=$?
	if [ ${sqlplusRetCode} -ne 0 ]
	then
		echo "Failed to execute query for deptno: [${deptNo}]. Return code: [${sqlplusRetCode}]."
		return ${sqlplusRetCode}
	fi
	return 0
}

arrayCountPos=0
arrayLastPos=${#FileName[@]}
while [ ${arrayCountPos} -lt ${arrayLastPos} ]
do
	currValue="${FileName[${arrayCountPos}]}"
	
	execSQLPlus "${currValue}"
	
	arrayCountPos=`expr ${arrayCountPos} + 1`
done

$
$
$ cat -n oraconnect.sh
  1  #!/usr/bin/bash
  2
  3  # Let's say the array has been populated at this point
  4  # Over here, I've populated the empno values from the EMP
  5  # demonstration table of the SCOTT sample schema of Oracle
  6  id_array[1]=7369
  7  id_array[2]=7839
  8  id_array[3]=7900
  9  id_array[4]=7654
 10
 11  # Generate all SQL statements by looping through the array
 12  # and assign that to a single shell variable
 13  SQLSTR=$(for i in "${id_array[@]}"; do
 14             echo "SELECT ename from emp where empno = $i;"'\n'
 15           done)
 16
 17  # now connect to Oracle and execute the SQL statements in
 18  # the string variable
 19  sqlplus -s /nolog <<EOF
 20  connect user/password@db
 21  set pages 0 heading off time off timing off feed off
 22  $(echo -e $SQLSTR)
 23  exit
 24  EOF
 25
$
$
$ ./oraconnect.sh
SMITH
KING
JAMES
MARTIN
$
$
$

$
$
$ # The Ids are in the files - file1 and file2
$
$ cat file1
7369
7839
$
$ cat file2
7900
7654
$
$
$ cat file1 file2
7369
7839
7900
7654
$
$
$ cat -n oraconnect1.sh
  1  #!/usr/bin/bash
  2
  3  # If the Ids are being fetched from files that have one Id
  4  # per line, then an array is not really needed.
  5  SQLSTR=$(cat file? | while read i; do
  6                         echo "SELECT ename from emp where empno = $i;"'\n'
  7                       done)
  8
  9  # now connect to Oracle and execute the SQL statements in
 10  # the string variable
 11  sqlplus -s /nolog <<EOF
 12  connect user/password@db
 13  set pages 0 heading off time off timing off feed off
 14  $(echo -e $SQLSTR)
 15  exit
 16  EOF
 17
$
$
$ ./oraconnect1.sh
SMITH
KING
JAMES
MARTIN
$
$
$

$
$ # For the text files - "file1" and "file2" posted earlier...
$
$ cat -n oraconnect2.sh
  1  #!/usr/bin/bash
  2
  3  # If the Ids are being fetched from files that have one Id
  4  # per line, then an array is not really needed.
  5  SQLSTR=$(cat file? |
  6           xargs printf "%s, " |
  7           sed 's/^\(.*\), $/SELECT ename FROM emp WHERE empno IN (\1);/')
  8
  9  echo "My query is: $SQLSTR"
 10
 11  # now connect to Oracle and execute the SQL statements in
 12  # the string variable
 13  sqlplus -s /nolog <<EOF
 14  connect user/password@db
 15  set pages 0 heading off time off timing off feed off
 16  $(echo -e $SQLSTR)
 17  exit
 18  EOF
 19
$
$
$ ./oraconnect2.sh
My query is: SELECT ename FROM emp WHERE empno IN (7369, 7839, 7900, 7654);
SMITH
MARTIN
KING
JAMES
$
$