Create DBMS_SCHEDULER job for oracle
Trying to create an assignment But can't compile it so I give this error. There is a question on the oracle forums, I say that I need to create a program to wrap it. Is there a workaround for this?
-- Created on 30.09.2014 by ALI.ORHAN declare -- Local variables here i integer; begin -- Test statements here dbms_scheduler.create_job(job_name => 'blabla' ,job_type => 'STORED_PROCEDURE' ,job_action => 'dingdongprocedure;' ,start_date => '30-OCT-14 10.00.00 PM' ,end_date => '15-JULY-08' ,repeat_interval => 'FREQ=WEEKLY BYDAY=TUE,FRI BYHOUR=10,13' ,enable => 'TRUE' ,comments => 'SUPREME COMMENT'); end;
After I created the assignment from the PL / SQL developer UI, I found out my syntax errors, new code is below;
- I am using sys.dbms_scheduler.create_job instead of dbms_scheduler.create_job. I don't know the difference, but this is not an important change.
- I used to_date to define start_date as a new starter, I found this best practice.
Important I have added the job_class parameter to 'DBMS_JOB $'. DBMS_JOB is the built-in Oracle RDBMS job class. So, you will find all tasks with this query:
select * from ALL_SCHEDULER_JOBS WHERE JOB_CLASS='DBMS_JOB$'
Important My spacing was wrong, you must put; between all parameters like
repeat_interval => freq=weekly;byhour=10, 13
- There is another syntax error in my first assignment. I am using enable instead of enabled .
I am setting auto_drop to false. I am guessing this parameter is used to cancel the job when it is doing its job. I mean if you create a job that changes daily changes to the next week every day. When the end time was reached, this work decreased. Please correct me if I am wrong.
sys.dbms_scheduler.create_job(job_name => 'BOMBASTICJOB' ,job_type => 'STORED_PROCEDURE' ,job_action => 'dingdongprocedure' ,start_date => to_date('30-09-2014 00:00:00' , 'dd-mm-yyyy hh24:mi:ss') ,end_date => to_date(null) ,job_class => 'DBMS_JOB$' ,repeat_interval => 'Freq=Weekly; ByDay=Tue, Fri; ByHour=10, 13' ,enabled => true ,auto_drop => false ,comments => '');
source to share
I'm at 126.96.36.199.0. You can create an assignment in a simple anonymous block:
SQL> BEGIN 2 DBMS_SCHEDULER.DROP_JOB (JOB_NAME => 'test_full_job_definition'); 3 END; 4 / PL/SQL procedure successfully completed. SQL> SQL> BEGIN 2 DBMS_SCHEDULER.create_job ( 3 job_name => 'test_full_job_definition', 4 job_type => 'PLSQL_BLOCK', 5 job_action => 'BEGIN my_job_procedure; END;', 6 start_date => SYSTIMESTAMP, 7 repeat_interval => 'freq=hourly; byminute=0; bysecond=0;', 8 end_date => NULL, 9 enabled => TRUE, 10 comments => 'Job defined entirely by the CREATE JOB procedure.'); 11 END; 12 / PL/SQL procedure successfully completed. SQL> SQL> SELECT JOB_NAME, ENABLED FROM DBA_SCHEDULER_JOBS where job_name ='TEST_FULL_JOB_DEFINITION' 2 / JOB_NAME ENABL ---------------------------------------- ----- TEST_FULL_JOB_DEFINITION TRUE SQL>
More examples here
source to share