I am stuck with a script where it has to send the notification mail only when select query returns some rows. If my select query returns zero rows it should not send me any mails.
I am briefing up the below script which runs through cron.
It would be a great sort of help if you can analyze and help me with a solution.The script is executing successfully but please help me with this requirement of sending mails when select query returns more than zero records.
I am eagerly waiting for your response.Please kindly do the needful.
- Code: Select all
#!/usr/bin/ksh
LOGFILE=path/name.log
WEEKLY_LOG=path/name.log
DBA='suhas@abc.com'
##Logs into all the databases automatically in the server.
sqlplus -s "/ as sysdba" <<EOF>$LOGFILE
set feed off;
set define ~
set echo on;
SET PAGESIZE 200
spool path/name.log append;
select sysdate from dual;
select host_name,instance_name,version from v\$instance;
select name from v\$database;
DROP TABLE owner.tablenameX1;
CREATE TABLE "owner"."tablenameX1"( columns );
DECLARE
variables
CURSOR part_data
IS
SELECT
FROM table 1
WHERE table_name in ( select table_name from table2 )
AND condition ;
BEGIN
Truncate the tablenameX1.
INSERT INTO owner.tablenameX1 (column names)
VALUES (from variables declared);
END LOOP;
COMMIT;
END;
/
select columns from owner.tablenameX1 group by columns having condition order by 3;
spool off;
exit;
EOF
done
cat $WEEKLY_LOG | mailx -s "subject : Description " $DBA
Regards,
Suhas
