I have pl/sql procedure that is written on the basis of dbms_jobs. It sends alerts when there is a problem with database. I need to rewrite this procedure so alert is not send right away but after a couple of tries.
Can anyone please help me?
Here is text of procedure:

create or replace procedure MOOSE_DB is

IO_ERROR EXCEPTION;
PRAGMA EXCEPTION_INIT (IO_ERROR, -204);

DEDICATED_PROCESS_FAILED EXCEPTION;
PRAGMA EXCEPTION_INIT (DEDICATED_PROCESS_FAILED, -12500);

HOST_NOT_FOUND EXCEPTION;
PRAGMA EXCEPTION_INIT (HOST_NOT_FOUND, -12545);

TNS_ERROR EXCEPTION;
PRAGMA EXCEPTION_INIT (TNS_ERROR, -12154);

LISTENER_ERROR EXCEPTION;
PRAGMA EXCEPTION_INIT (LISTENER_ERROR, -12541);

UNABLE_TO_CONNECT EXCEPTION;
PRAGMA EXCEPTION_INIT (UNABLE_TO_CONNECT, -2068);

NO_DB_LINK EXCEPTION;
PRAGMA EXCEPTION_INIT (NO_DB_LINK, -2019);

MAX_PROCESSES_EXCEDED EXCEPTION;
PRAGMA EXCEPTION_INIT (MAX_PROCESSES_EXCEDED, -20);

ARCHIVER_ERROR EXCEPTION;
PRAGMA EXCEPTION_INIT (ARCHIVER_ERROR, -257);

cursor db is
select rtrim(sid) as sid , rtrim(host) as host, rtrim(department) as department, rtrim(server_group) as server_group, primary_dba from db_instances where alert_status = 'A' and status = 'ONLINE' ;
db_rec db%rowtype;

cursor db_alert_mail_server is
select smtp_server_name, pop3_imap4_server_name, alert_email_address from db_alert_mail_server;
db_alert_mail_server_rec db_alert_mail_server%rowtype;

cursor db_alert_moose is
select phone_email_address from db_alert_list where JOB_TITLE = 'MOOSE';
db_alert_moose_rec db_alert_moose%rowtype;

cursor db_alert_dba is
select email_address, phone_email_address from db_alert_list where NAME = db_rec.primary_dba;
db_alert_dba_rec db_alert_dba%rowtype;

cursor db_alert_dba_manager is
select email_address from db_alert_list where JOB_TITLE = 'DBA MANAGER';
db_alert_dba_manager_rec db_alert_dba_manager%rowtype;

stmt varchar2(2000);

rows number;
name_var varchar2(12);
version_var varchar2(66);
sqlcursor number;
sqlcursor2 number;
dblink varchar2(60);


begin
open db;
fetch db into db_rec;

open db_alert_mail_server;
fetch db_alert_mail_server into db_alert_mail_server_rec;

open db_alert_dba_manager;
fetch db_alert_dba_manager into db_alert_dba_manager_rec;

open db_alert_moose;
fetch db_alert_moose into db_alert_moose_rec;


while not db%NOTFOUND loop

open db_alert_dba;
fetch db_alert_dba into db_alert_dba_rec;

dblink := db_rec.sid||'.'||db_rec.host;
sqlcursor := dbms_sql.open_cursor;

stmt:='select dbid as dbid, log_mode as log_mode from v$database@'||dblink;

dbms_sql.parse(sqlcursor, stmt, 1);

dbms_sql.define_column(sqlcursor, 1, name_var, 12);

rows := dbms_sql.execute_and_fetch(sqlcursor);

dbms_sql.column_value(sqlcursor, 1, name_var);

dbms_sql.close_cursor(sqlcursor);

fetch db into db_rec;
close db_alert_dba;

end loop;


close db;
close db_alert_mail_server;
close db_alert_dba_manager;
close db_alert_moose;

EXCEPTION

WHEN IO_ERROR
THEN

update db_instances
set status_details = 'IO ERROR',
status = 'OFFLINE',
alert_time = sysdate(),
alert_number = 1
where sid = db_rec.sid and host = db_rec.host;
commit;

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_moose_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - IO ERROR');

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - IO ERROR');

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - IO ERROR');

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_manager_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - IO ERROR');

close db;
close db_alert_mail_server;
close db_alert_dba_manager;
close db_alert_moose;

MOOSE_DB;








WHEN DEDICATED_PROCESS_FAILED
THEN

update db_instances
set status_details = 'DEDICATED PROCESS UNABLE TO START',
status = 'OFFLINE',
alert_time = sysdate(),
alert_number = 1
where sid = db_rec.sid and host = db_rec.host;
commit;

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_moose_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - DEDICATED PROCESS UNABLE TO START');

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - DDEDICATED PROCESS UNABLE TO START');

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - DEDICATED PROCESS UNABLE TO START');

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_manager_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - DEDICATED PROCESS UNABLE TO START');

close db;
close db_alert_mail_server;
close db_alert_dba_manager;
close db_alert_moose;

MOOSE_DB;


WHEN HOST_NOT_FOUND
THEN

update db_instances
set status_details = 'HOST NOT FOUND',
status = 'OFFLINE',
alert_time = sysdate(),
alert_number = 1
where sid = db_rec.sid and host = db_rec.host;
commit;

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_moose_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - HOST NOT FOUND');

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - HOST NOT FOUND');

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - HOST NOT FOUND');

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_manager_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - HOST NOT FOUND');

close db;
close db_alert_mail_server;
close db_alert_dba_manager;
close db_alert_moose;

MOOSE_DB;

WHEN UNABLE_TO_CONNECT
THEN

update db_instances
set status_details = 'Unable to Connect',
status = 'OFFLINE',
alert_time = sysdate(),
alert_number = 1
where sid = db_rec.sid and host = db_rec.host;
commit;


dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_moose_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - UNABLE TO CONNECT');

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - UNABLE TO CONNECT');

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - UNABLE TO CONNECT');

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_manager_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - UNABLE TO CONNECT');

close db;
close db_alert_mail_server;
close db_alert_dba_manager;
close db_alert_moose;

MOOSE_DB;


WHEN TNS_ERROR
THEN

update db_instances
set status_details = 'TNS ERROR',
status = 'OFFLINE',
alert_time = sysdate(),
alert_number = 1
where sid = db_rec.sid and host = db_rec.host;
commit;

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_moose_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - TNS ERROR');

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - TNS ERROR');

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - TNS ERROR');

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_manager_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - TNS ERROR');


close db;
close db_alert_mail_server;
close db_alert_dba_manager;
close db_alert_moose;

MOOSE_DB;


WHEN LISTENER_ERROR
THEN

update db_instances
set status_details = 'LISTENER ERROR',
status = 'OFFLINE',
alert_time = sysdate(),
alert_number = 1
where sid = db_rec.sid and host = db_rec.host;
commit;

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_moose_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - LISTENER ERROR');

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - LISTENER ERROR');

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - LISTENER ERROR');

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_manager_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - LISTENER ERROR');


close db;
close db_alert_mail_server;
close db_alert_dba_manager;
close db_alert_moose;

MOOSE_DB;



WHEN NO_DB_LINK
THEN

update db_instances
set status_details = 'NO DB LINK',
status = 'OFFLINE',
alert_time = sysdate(),
alert_number = 1
where sid = db_rec.sid and host = db_rec.host;
commit;

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_moose_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - NO DB LINK');

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - NO DB LINK');

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - NO DB LINK');

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_manager_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - NO DB LINK');

close db;
close db_alert_mail_server;
close db_alert_dba_manager;
close db_alert_moose;

MOOSE_DB;

WHEN MAX_PROCESSES_EXCEDED
THEN

update db_instances
set status_details = 'MAX PROCESSES EXCEDED',
status = 'OFFLINE',
alert_time = sysdate(),
alert_number = 1
where sid = db_rec.sid and host = db_rec.host;
commit;

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_moose_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid ,
db_rec.host||'.'||db_rec.sid||' - MAX PROCESSES EXCEDED');

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid ,
db_rec.host||'.'||db_rec.sid||' - MAX PROCESSES EXCEDED');

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid ,
db_rec.host||'.'||db_rec.sid||' - MAX PROCESSES EXCEDED');

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_manager_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid ,
db_rec.host||'.'||db_rec.sid||' - MAX PROCESSES EXCEDED');

close db;
close db_alert_mail_server;
close db_alert_dba_manager;
close db_alert_moose;

MOOSE_DB;

WHEN ARCHIVER_ERROR
THEN

update db_instances
set status_details = 'ARCHIVER ERROR',
status = 'OFFLINE',
alert_time = sysdate(),
alert_number = 1
where sid = db_rec.sid and host = db_rec.host;
commit;

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_moose_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - ARCHIVER ERROR');

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - ARCHIVER ERROR');

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_rec.phone_email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - ARCHIVER ERROR');

dbms_mail.init(
db_alert_mail_server_rec.smtp_server_name,
db_alert_mail_server_rec.pop3_imap4_server_name,
db_alert_mail_server_rec.alert_email_address);
dbms_mail.sendtextmsg(
db_alert_dba_manager_rec.email_address,
'WARNING: '||db_rec.host||'.'||db_rec.sid,
db_rec.host||'.'||db_rec.sid||' - ARCHIVER ERROR');

close db;
close db_alert_mail_server;
close db_alert_dba_manager;
close db_alert_moose;

MOOSE_DB;

end MOOSE_DB;