Hi guys I've been given a task to automate a service which is currently manually carried out by myself each week but I've only started looking into scheduling today. With this task I enter database names in a shell which is then used in a script (.ksh file) to determine the databases size. At the moment I press a few key strokes (2, 'db name', enter) and repeat until I'm done (around 20 times). I then send an email to a colleague listing the databases which failed to commit. My plan is to create a new .ksh looping over the file and then calling the previous .ksh with the database name as a parameter. The new .ksh would have a variable keeping a list of the failed attempts and would then be emailed (although I'm not sure whether this would be in the schedule or the .ksh as I don't know if the .ksh can return a variable to the place it was called from (schedule).
So far I've found an example of a schedule on the computer:
begin
dbms_scheduler.create_job(
job_name=>'prod_con_cstrnpa',
job_type=>'plsql_block',
job_action=>'insert into corcon01_hour_chg_totals
(db_name,ins_date,td_hr,cou_iso_id,user_id,total,type,cou_iso_id_dest)
select ''CSTRNPA'',current_date,to_char(con_add_td, ''YYYY-MM-DD HH24''),cou_iso_id_orig,con_add_user_id,count(*),''CON'',cou_iso_id_dest
from corcov01@XXX.COM
where CON_ADD_TD >= TRUNC(CURRENT_DATE-1)
and CON_ADD_TD < TRUNC(CURRENT_DATE-1+1)
group by to_char(con_add_td, ''YYYY-MM-DD HH24''), cou_iso_id_orig, con_add_user_id,cou_iso_id_dest;',
start_date=>sysdate,
repeat_interval=>'freq=daily; interval=1; byhour=1; byminute=5',
enabled=>true,
auto_drop=>false);
end;
/
and an example of an email
here (bottom). Is it then possible to combine the two? E.g.:
begin
dbms_scheduler.create_job(
job_name=>'EMAIL_TEST',
job_type=>'UNSURE',
job_action=>'BEGIN
UTL_MAIL.send(sender => 'aaa@pncl.com',
recipients => 'bbb@pncl.com',
cc => 'bbb@pncl.com',
bcc => 'ccccc@bellsouth.net',
subject => 'UTL_MAIL Test',
message => 'Hi, this is an email from Oracle on server PNCLULTRAPRD!');
END;
/',
start_date=>sysdate,
repeat_interval=>'freq=daily; interval=1; byhour=1; byminute=5',
enabled=>true,
auto_drop=>false);
end;
/
All I want to do at the moment is get something working which would send myself an email in 10 minutes time. Thanks for any help.
Mike
Edited by: Dird on 27-Jul-2009 02:42