[SOLVED] unix script that executes sql query in a while loop

smritisingh03 · 01-08-2011, 04:04 AM

Hi All.I have a problem here.

I have a logfile which looks like:

tableabc 1786rows
tabledfg 8374rows
tablefhd 726rows
......
....
.....

Now i have to writ a script which takes this log as input and quesries the DB for each table.
So,I want to:

input the logfile
start a while loop.So,while read LINE
This variable LINE would store the table name by CUT using a delimiter
once the table name is ready it makes a connection to DB and queries the table for its rowcount
writes the rowcount to an O/P file

Please help as I am new to IT industry and very new to scripting.

carltm · 01-08-2011, 04:52 AM

Have you started to work on this? My suggestion would be to start
writing the script and then ask for assistance when you need help
with a specific part.

smritisingh03 · 01-08-2011, 09:16 AM

yes i have written a script but this script prints only the table name but does not query that table for its rowcount.

#!/bin/ksh

SQL_CONN_CODE="user/pass@Instance"

while read LINE

do

table_name=`echo $LINE|cut -d "-" -f1`

sqlplus -s "user/pass@Instance" <<EOF>> DBcountOP

set heading off feedback off pagesize 0 linesize 30000 trimout on;

row_count=`echo "select count(*) from $table_name;"

print $table_name $row_count >> DBcountOP

done < logcountOP

EOF
~

druuna · 01-08-2011, 09:46 AM

Hi,

You are trying to mix and match a here document and normal shell commands, which is not possible (the shell/sql won't be able to figure out which is which).

Have a look at the following:

Code:

#!/bin/bash

cat logcountOP | \
while read TblName RCDummy
do
  echo -n "${TblName} : "
  sqlplus -s sa/sa@conCRM5 <<SQLScript
     set heading off feedback off pagesize 0 linesize 30000 trimout on;
     select count(*) from ${TblName};
SQLScript
done > DBcountOP

I'm assuming that the set heading off .... line is needed and doesn't generate output.

If you use 2 variables after the while read, those are filled with the first and second field from the logcountOP file. This way there is no need for a cut command.
The echo -n "${TblName} : " echo's the table name, the -n makes sure there is no carriage return.
Everything in the here document (all between <<SQLScript and SQLScript) is given to sql.

Hope this helps.

EDIT:
If you have a very large number of tables that need to be checked, this approach might not be what you want. The above example will open and close a sql session for every table that needs to be checked.

Creating a sql file which holds all the sql commands and giving this to sql would be a better solution, but also a bit harder to implement.