Trying to create job But can't compile it keeps me given this error. There is a question on oracle forums, it say's that i have to create program to wrap it. Is there any workaround for this?
<img src="https://i.stack.imgur.com/D3VFL.png" alt="It gives me this Error">
-- 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 job from PL/SQL Developer UI, i found out my syntax erorrs, new code is below;<ol> <li>i use sys.dbms_scheduler.create_job instead of dbms_scheduler.create_job. I don't know differances but it's not important alteration.</li> <li>i used to_date to define start_date, as a fresh-starter i found this better practise.</li> <li>
<strong>Important</strong> I added job_class parameter to 'DBMS_JOB$'. DBMS_JOB is built_in job class of Oracle RDBMS. So you find all jobs with this query:
select * from ALL_SCHEDULER_JOBS WHERE JOB_CLASS='DBMS_JOB$'</li> <li>
<strong>Important</strong> My interval's were wrong you should put ; between all parameters like
repeat_interval => freq=weekly;byhour=10, 13</li> <li>My first job code has another syntax error i use <strong>enable</strong> instead of <strong>enabled</strong>.</li> <li>
I set auto_drop false. I guess this parameter is used to drop job when it dones his job. I mean if you create a job that makes changes daily from today to next week. After end-time reaches, this job has dropped. Please correct me if i 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 => '');</li> </ol>
I am on 188.8.131.52.0. You could create the job 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