UNIX Script required for count the records in table

Shell Programming and Scripting
1

Hi Friends,

I looking for the script for the count of the records in table.

and then it's containg the zero records then should get abort.

and should notify us through mail.

Can you please help me out in this area i am lacking.

2

I am assuming a database table.

We need the following information to give you a useful answer:

  1. What database - Oracle, Sql server, etc.?

  2. What shell do you use - bash, ksh, etc.?

3

Is this a homework assignment?

4

I have written below script plz check it and correct it if any.

#!/bin/sh
count=`sqlplus $username/$password@$tnsname << EOF
select count(*) from table;
exit;
EOF`
if [ $count = 0 ]
then 
exit 1
fi
mailx -s " record count " abc@xyz.com < $count
exit 0
5

We are not here to write and debug your programming projects. If you make an honest effort and need help, we'll be happy to help you.

What error message(s) did your script produce when you ran it?

Can sqlplus fail (leaving count unset)? If it can, the if test will probably yield a syntax error. (Don't you think you should quote the expansion of $count ?

How does file redirection work? If sqlplus says you have 27 records in your table, do you have a file named 27? If so, do you want the mail message to contain the contents of the file named 27, or do you want the message to be 27 ? If mailx reads the message from standard input, how would you change:

mailx -s " record count " abc@xyz.com < $count

to send a message containing the text 27 instead of sending the contents of a file named 27?

2 Likes
6

What output do you get if you don't try to capture the output from sqlplus into the variable. My guess is that there are all sorts of headers and general information being written out.

What do you get (paste it in CODE tags please), and how will you handle it or trim it down?

Have a look at the options for calling sqlplus to see if that helps. You can get this by running sqlplus -?

Robin