To receive email notification when there are rows returned

All posts relating to Oracle database administration.

Moderator: Tim...

To receive email notification when there are rows returned

Postby suhas_k_p » Tue Apr 03, 2012 2:40 am

Hi Tim,
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
suhas_k_p
Member
 
Posts: 13
Joined: Thu Dec 29, 2011 12:56 pm
Location: India

Re: To receive email notification when there are rows return

Postby Tim... » Tue Apr 03, 2012 8:01 am

Hi.

I assume you are talking about the final query returning zero rows being the test you are interested in. If so, then take it out of the existing "sqlplus ... <<EOF ... EOF" call and put it in its own, that way you can check the output specifically for that call. See here:

http://www.oracle-base.com/articles/mis ... nix_output

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: To receive email notification when there are rows return

Postby suhas_k_p » Fri Apr 06, 2012 8:30 am

Hi Tim,
Thanks a ton for the logic. It worked.

Regards,
Suhas
suhas_k_p
Member
 
Posts: 13
Joined: Thu Dec 29, 2011 12:56 pm
Location: India

Re: To receive email notification when there are rows return

Postby Tim... » Fri Apr 06, 2012 9:45 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 5 guests

cron