This forum is currently locked. You can't register or post questions at this time. (read more)

To receive email notification when there are rows returned

All posts relating to Oracle database administration.

Moderator: Tim...

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

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

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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

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 suhas_k_p » Fri Apr 06, 2012 8:30 am

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

Regards,
Suhas

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 4 guests