为Oracle创建DBMS_SCHEDULER作业 [英] Creating DBMS_SCHEDULER job for oracle
问题描述
尝试创建作业,但无法编译,这使我一直遇到此错误.在oracle论坛上有一个问题,它说我必须创建程序来包装它. 这有什么解决方法?
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?
-- 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;
从PL/SQL Developer UI创建作业后,发现语法错误,下面是新代码;
After i created job from PL/SQL Developer UI, i found out my syntax erorrs, new code is below;
- 我使用sys.dbms_scheduler.create_job代替dbms_scheduler.create_job.我不知道差异,但这不是重要的变更.
- 我以前使用to_date来定义start_date,作为新手,我发现了这种更好的做法.
-
重要我将job_class参数添加到"DBMS_JOB $". DBMS_JOB是Oracle RDBMS的内置作业类.因此,您可以通过以下查询找到所有作业:
- i use sys.dbms_scheduler.create_job instead of dbms_scheduler.create_job. I don't know differances but it's not important alteration.
- i used to_date to define start_date, as a fresh-starter i found this better practise.
Important 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$'
重要您应该输入我的间隔错误;在所有参数之间,例如
Important My interval's were wrong you should put ; between all parameters like
repeat_interval => freq=weekly;byhour=10, 13
我将auto_drop设置为false.我猜想这个参数是在完成工作时用于删除工作的.我的意思是,如果您创建的工作每天从今天到下周进行更改.达到结束时间后,这项工作就减少了.如果我错了,请纠正我.
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 => '');
推荐答案
我现在是12.1.0.1.0.您可以在一个简单的匿名块中创建作业:
I am on 12.1.0.1.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>
更多示例此处
这篇关于为Oracle创建DBMS_SCHEDULER作业的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!