Shell Programming and Scripting

Hello

I am a novice shell scripting programmer, so please bare with me.

I have embedded a simple SQL statement into a shell script, which simply returns an integer (its a count (*) statement).

The result of the statement is then oputput to .txt file. So, the number could be 0, 1,2, 10, 50, 101 etc.

What I want to do is send an email if the number output into the .txt file is greater than 0. How do I check that the number output is 0, I thought about checking whether the file contained a 0, but I thought this may not work as it may also return a true value if the number was '10' or '100' etc.

Thanks

You could try something like this, though admittedly, I am not sure if VALUE will actually contain the result you are looking for. In your sql file, you will need to turn headings off (assuming you are using Oracle) and set server output on:

Code:

VALUE=$(sqlplus -s user/password@test_id <<EOF
@my_sql_statement
EOF)

if [ $VALUE -gt 0 ]
 then
   email me
fi

If this didnt work for you, then you could try spooling the value of your sql statement and then parse the spool file for your result. You could do this using an number of tools such as grep, awk, cut, etc. The idea is the same, search for the pattern and evaluate if it is greater than 0.

if tmp.txt is the file of yr sql o/p;

then ....

i=`cat x`
echo "i=$i"
if test $i -gt 0
then
echo "O/P is greater than 0" > y
sendmail emailname@x.com < y
fi

Assuming Oracle is your database. The following code will do the trick. Just tested it in my environment. Note, sqlplus -s will put sqlplus into silent mode, running without this option will cause the database version and other Oracle information to be displayed when logging into the database. For this code to work, you will want to suppress those messages. You can call an external SQL file instead of embedding the sql as I have done below. The syntax for this is shown in my first post. You can also add a path to the file if you so choose. Happy coding....

Code:

#!/bin/ksh
    
VALUE=$( sqlplus -s userid/passwd <<EOF
 set heading off;
 set serveroutput on;
 select count(*) from my_table;
 quit
EOF)
    
if [ $VALUE -gt 0 ]
then
  echo "Value was Greater than 0. Value=[ $VALUE ]"
  add_email_code_here
else
  echo "Value was Less than 0. Value=[ $VALUE ]"
fi

Thanks guys

Google, I am actually using a syabse databse in this case. I shall give the code a go, but I am not very famiar with either Oracle or Sybase, so don't really know the ins and outs.

Thanks for your help