Add job to scheduler to invoke that job automatically everyday

Question

I have a created a job which runs on everyday at 09:00:00.

Below is the snippet i have used to create a job :

BEGIN
            Dbms_Scheduler.create_job(
                job_name   => 'PROECSS_STATE_ARCH'
               ,job_type   => 'STORED_PROCEDURE' 
               ,job_action   => 'TEST' -- Procedure Name
               ,start_date   => SYSDATE 9:00:00
               ,repeat_interval => 'freq=DAILY'
               ,enabled   => TRUE
               ,comments   => 'job schedule for archiving process_state');
END;

sole purpose of this is to run a stored procedure 'TEST' on everyday at 09:00:00

how to add this job to scheduler to invoke this job automatically on everyday? Can anyone please tell me ??

Shouldn't that be PROCESS_STATE_ARCH ? – Frank Schmitt Mar 10 '14 at 11:40 — Frank Schmitt, Mar 10 '14 at 11:40
Ya, job name is process_state_arch. – vkreddy Mar 10 '14 at 12:27 — vkreddy, Mar 10 '14 at 12:27

Dba · Answer 1 · 2014-03-10T10:57:47.527

0

You need to specify byhour too to run the job everyday at 9 pm.

BEGIN
            Dbms_Scheduler.create_job(
                job_name   => 'PROECSS_STATE_ARCH'
               ,job_type   => 'STORED_PROCEDURE' 
               ,job_action   => 'TEST' -- Procedure Name
               ,start_date   => SYSDATE
               ,repeat_interval => 'freq=DAILY; byhour=9' --Added byhour
               ,enabled   => TRUE
               ,comments   => 'job schedule for archiving process_state');
END; 
/

To run this on your scheme, you need CREATE JOB privilege. Otherwise you need to run this on any other privileged user. In that case specify job_action as,

job_action   => '<SCHEMA_NAME>.TEST'

To edit the job, you have the SET_ATTRIBUTE procedure in DBMS_SCHEDULER package.

Eg:-

BEGIN
     DBMS_SCHEDULER.SET_ATTRIBUTE (
          name => 'PROECSS_STATE_ARCH',
          attribute => 'repeat_interval',
          value => 'freq=DAILY; byhour=10' --Everyday by 10 AM.
          ); 
END;

edited Mar 10 '14 at 10:57

answered Mar 10 '14 at 10:05

Dba

6,511
1
24
33

Thank you. Will it run daily if i execute above code snippet? Where this job will be stored if i want to edit later. Any idea ? – vkreddy Mar 10 '14 at 10:43
Yes i don't have privilege to create a job. Thank you:) – vkreddy Mar 10 '14 at 10:49
@vkreddy, Yeah, this will run everyday at 9:00 AM. I've added an example for how to edit a created job in my answer. – Dba Mar 10 '14 at 10:57
Job has been created Successfully. But the procedure has not executed. Plz suggest @Dba – vkreddy Mar 21 '14 at 12:43

Add job to scheduler to invoke that job automatically everyday

1 Answers1