LinuxQuestions.org - [SOLVED] unix script that executes sql query in a while loop

- Linux - Newbie (https://www.linuxquestions.org/questions/linux-newbie-8/)

- - unix script that executes sql query in a while loop (https://www.linuxquestions.org/questions/linux-newbie-8/unix-script-that-executes-sql-query-in-a-while-loop-854963/)

unix script that executes sql query in a while loop

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.

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.

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
~

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.