Script which will run every 10minutes and Send SMS to mobile

UNIX for Advanced & Expert Users
1

Hello Genuis guys,

I am running Aix5 and Oracle 9 on it.

I have sql query which only count a particular rows.

I need to prepare a script which will run that sql query every 10 minutes and if the ouput count is greate than 20. It will send SMS to mobile numbers.

Need your kind help to prepare this.

--Purple

2

Important questions here:

  • Do you have the count script ready? Or at least attempted to write it? Because we won't write it for you.
  • What attempts did you do so far, and where did they fail? Because, again, we won't write it for you.
  • What command is used to send out SMS? Because without a gateway of some sorts you won't be able to send messages to a mobile network.
3

First of all I newbie in the shell script. I just need your valuable code. I can then try to modify accordingly if necessary.

  1. Yes I have that count script ready. you definately won't write it for me :smiley:

  2. I have SMS gateway. I can put the gateway address in that script.

Please kindly show me the script.

4

Just add an entry into your crontab file

For AIX you can locate the crontab in /var/spool/cron/crontabs, each file contains scheduled tasks for each user

best regards

5

The basic logic would be something like this:

# Untested, since I've neither got Oracle nor an SMS gateway available
count=$( sqlplus -s user/pw@sid @count_script )
if [ $count -gt 20 ]
then
    # Command to send SMS here
fi

Set up a cron job to run this every 10 minutes and that should do it.

6

for running at 10mins interval i will add it in crontab. Thats not a problem.

but just to need the script which will fetch the output and send sms. I will first try to save the output in log file. Please tell How to put date and time and append the output in one file" ??

Many Thanks pludi. I will try your scriipt.

7

Try the 'date' command. It will work by itself, and if you need a specific format, see 'man date' for all its options.

$ date
Wed Nov 10 15:03:46 CST 2010
$ date "+%Y-%m-%d"
2010-11-10
$
1 Like
8

Hi,

I just firt tried this way. But it giving errors

#! /bin/ksh
su - oracle
count=$( sqlplus -s abc/abc@mydb @select count(*) from xyz.order where create_date < sysdate - 1/720; )

But output has error Syntax error: `(' is not expected.

how to fix?

Meanwhile in IF THEN statement I want to append output count in simple txt file. How to add?
if [ $count -gt 20 ]
then
# Coutput will be in txt file. And will append the output in same file
fi

---------- Post updated at 04:30 PM ---------- Previous update was at 03:16 PM ----------

Hi guys,

i do able to connect to oracle. Infact, i need to export env variable.

[i]#! /bin/ksh
export ORACLE_HOME=/opt/oracle/app/oracle/product/9.2
/opt/oracle/app/oracle/product/9.2/bin/sqlplus -s abc/abc@mydb @myquery.sql
exit

But exit is not working here. I need to put manually exit command. What to do?

---------- Post updated at 04:47 PM ---------- Previous update was at 04:30 PM ----------

Sorry guys for too many message. i will write my query in other message. Ignore this THREAD. Moderator also can delete it.

9

are you exiting properly in your @myquery.sql file ?