Mail from unix box

Shell Programming and Scripting
1

I am executing the below script from unix box

If row count is zero the mail should not set to the client

The problem is, if row count is zero the mail is going to the client
(what is wrong in the script)

SET HEAD OFF FEED OFF PAGES 0
Select count(*) as change_count from DBG_INITIATIVES_CHANGE;"

change_count=${output}

echo $change_count

if [ ${change_count} > 0 ]; then

mailx -s aaa@yahoo.com

EOF

exit 1

fi

exit 0

2

Can you modify your script a bit....

X=`sqlplus -s user/pwwd@domain<<eof
set serveroutput on;
set heading off;
DECLARE
change_count number;
BEGIN
Select count(*) into change_count from DBG_INITIATIVES_CHANGE;
dbms_output.put_line(change_count);
END;
/
EXIT;
eof`
if [ $X -gt 0 ]
then
#put ur mail command here
else
echo "count is zero"
fi
3

This is my output
SQL> Select count(*) as change_count from DBG_INITIATIVES_CHANGE;

CHANGE_COUNT
------------
0

I have to check CHANGE_COUNT is 0 If it is 0 then I should not send mail

4

Yes the above script is doing what you say it should it will login to sqlplus execute the query and thru dbms output package X will have value of change_count and it will send mail if change_count is greater then zero.

sqlplus -s user/pwwd@domain<<eof in this line give your user name password and domain.run the below code as shell script.

X=`sqlplus -s user/pwwd@domain<<eof
set serveroutput on;
set heading off;
DECLARE
change_count number;
BEGIN
Select count(*) into change_count from DBG_INITIATIVES_CHANGE;
dbms_output.put_line(change_count);
END;
/
EXIT;
eof`
if [ $X -gt 0 ]
then
mailx -s "count is greater than zero" abcd@xyz.com
else
echo "count is zero"
fi
5

I have to grep the output If output is zero(0) I should not send mail

If ouput is > 0 the mail should send to client

You are using plsql scripting

(My problem is to restrict the mail only)

6

The same without PL/SQL :

X=`sqlplus -s user/pwwd@domain<<eof
SET HEAD OFF FEED OFF PAGES 0
Select count(*) from DBG_INITIATIVES_CHANGE;
eof`
if [ $X -gt 0 ]
then
mailx -s "count is greater than zero" abcd@xyz.com
else
echo "count is zero"
fi

Jean-Pierre.

7

Hi

Can anyone tell me what does following line signify :

X=`sqlplus -s user/pwwd@domain<<eof

Thanks & Regards

Pankaj

8

Execute the sqlplus command and store it's output into variable X.
The sqlplus command is started with the -s option that means silent mode.
The user and passord for connection to the database is specified on the command line user/pwwd@domain

The syntax <<eof specifies that the input for the sqlplus command is included in the script starting next line and ending with the line eof

Jean-Pierre.

9

Aigles is absolutely right what he has explained i would like to add few lines.

sqlplus -s user/pwwd@domain

this line of code will invoke sqlplus and will login to database with "user" and "passwd" in "domain".
now you are in sqlplus and run procedure,functions etc as you can do it in
sqlplus session.

<<eof
.......
.........
......
eof
this is known as here document.

if we do any print as in unix is echo and sqlplus is dbms_output.put_line will come to shell variable X.

10

mailx -s "Please check" test@yahoo.com

After giving this command on unix box whether i have to put any control key
mail is not sending