Review your favorite Linux distribution.
Go Back > Forums > Linux Forums > Linux - Newbie
User Name
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!


  Search this Thread
Old 01-08-2011, 04:04 AM   #1
Registered: Nov 2010
Posts: 43

Rep: Reputation: 0
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.
Old 01-08-2011, 04:52 AM   #2
Registered: Jan 2007
Location: Canton, MI
Distribution: CentOS, SuSE, Red Hat, Debian, etc.
Posts: 703

Rep: Reputation: 97
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.
Old 01-08-2011, 09:16 AM   #3
Registered: Nov 2010
Posts: 43

Original Poster
Rep: Reputation: 0
yes i have written a script but this script prints only the table name but does not query that table for its rowcount.



while read LINE


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


Last edited by smritisingh03; 01-08-2011 at 09:49 PM.
Old 01-08-2011, 09:46 AM   #4
LQ Veteran
Registered: Sep 2003
Posts: 10,532
Blog Entries: 7

Rep: Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392Reputation: 2392

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:

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

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.

Last edited by druuna; 01-08-2011 at 09:51 AM.


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Bash script: Need some help on how to parse a simple SQL query newbie_0404 Linux - Newbie 10 06-02-2010 12:31 AM
unix shell script loop concept badrinath_tcs Linux - Software 3 10-01-2009 07:11 PM
Loop or check SQlite3 database continuously, when query found run script for first 3 Techno Guy Linux - Newbie 3 09-27-2009 07:46 PM
Speeding up the script, or the SQL Query? knickers Programming 1 04-13-2004 11:57 AM
ColdFusion - Using a script to build and execute SQL query when a button is clicked Locura Programming 1 02-25-2004 09:59 PM > Forums > Linux Forums > Linux - Newbie

All times are GMT -5. The time now is 06:24 AM.

Main Menu
Write for LQ is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration