1

I have created one stored procedure with name

traffic_details_temp_send_mail;

How to make this procedure to run everyday at 10AM?

Please help with block of code.

Thanks in advance.

Shreedhar
  • 261
  • 2
  • 6
  • 15

1 Answers1

11

you can create a scheduler job:

begin
    dbms_scheduler.create_job(job_name        => 'TRAFFIC_DETAILS_JOB',
                              job_type        => 'STORED_PROCEDURE',
                              job_action      => 'traffic_details_temp_send_mail',
                              start_date      => systimestamp,
                              end_date        => null,
                              repeat_interval => 'freq=daily; byhour=10; byminute=0; bysecond=0;',
                              enabled         => true,
                              auto_drop       => false,
                              comments        => 'your description here.');
end;
/

then you can see the details in the scheduler job views (user_scheduler_jobs etc). see here for information on scehduler jobs.

DazzaL
  • 21,638
  • 3
  • 49
  • 57
  • Dazzal. What is the difference in DBMS job scheduling and DBA job scheduling – Shreedhar Mar 13 '13 at 12:27
  • @Shreedhar `dbms_job` is the older job scheduler with not as good logging and scheduling capabilities. i'd use scheduler if i were you for any new jobs you want (also Oracle's recommendation is that you don't use `dbms_job` aanymore..but its still supported) – DazzaL Mar 13 '13 at 13:04
  • @Shreedhar One difference is that dbms_scheduler performs a commit when adding a job, where dbms_job does not. http://stackoverflow.com/a/4153606/121544 – Shannon Severance Apr 14 '15 at 21:56