我的目的是每分钟执行一次'ccs_synproc'这个存储过程,环境oracle 10gdeclare
jobsynproc number;
begin
dbms_job.submit(job => jobsynproc,
what => 'ccs_synproc',
next_date => to_date('30-12-2011 00:00:00','dd-mm-yyyy hh24:mi:ss'),
interval => TRUNC(sysdate, 'mi') + 1 / (24*60),
no_parse => false);
commit;
end;
执行 时报错:parameter value "03-1月 -12" is not appropriate
我这样select to_date('30-12-2011 00:00:00', 'dd-mm-yyyy hh24:mi:ss') from dual;是可以成功执行的求教
jobsynproc number;
begin
dbms_job.submit(job => jobsynproc,
what => 'ccs_synproc',
next_date => to_date('30-12-2011 00:00:00','dd-mm-yyyy hh24:mi:ss'),
interval => TRUNC(sysdate, 'mi') + 1 / (24*60),
no_parse => false);
commit;
end;
执行 时报错:parameter value "03-1月 -12" is not appropriate
我这样select to_date('30-12-2011 00:00:00', 'dd-mm-yyyy hh24:mi:ss') from dual;是可以成功执行的求教
修改成
next_date => to_date('30-12-2011 00:00:00','dd/mm/yyyy hh24:mi:ss'),
declare
jobsynproc number;
begin
dbms_job.submit(job => jobsynproc,
what => 'ccs_synproc;',
next_date => to_date('30-12-2011 00:00:00','dd/mm/yyyy hh24:mi:ss'), interval => TRUNC(sysdate, 'mi') + 1 / (24*60),
no_parse => false);
commit;
end;
2012-01-03 12:52:00
jobsynproc number;
begin
dbms_job.submit(job => jobsynproc,
what => 'begin ccs_synproc; end;',
next_date => to_date('30-12-2011 00:00:00','dd-mm-yyyy hh24:mi:ss'),
interval => 'trunc(sysdate,''mi'')+1/(24*60)'
);
commit;
end;
job OUT BINARY_INTEGER,
what IN VARCHAR2, NEXT_DATE IN DATE DEFAULTSYSDATE,
interval IN VARCHAR2 DEFAULT 'NULL',
no_parse IN BOOLEAN DEFAULT FALSE,
instance IN BINARY_INTEGER DEFAULT ANY_INSTANCE,
force IN BOOLEAN DEFAULT FALSE);
interval 是个字符串,oracle拿他来拼动态sql的
DBMS_SCHEDULER.CREATE_JOB(job_name => 'jobsynproc', --job名称,自己设
job_type => 'STORED_PROCEDURE', --类型为存储过程
job_action => 'ccs_synproc', --存储过程名称为proc
start_date => to_date('30-12-2011 00:00:00',
'dd-mm-yyyy hh24:mi:ss'), --开始执行时间
enabled => TRUE, --自动启用
auto_drop => false,
repeat_interval => 'FREQ=Monthly;Interval=1');
END;