dbms_job can not run

[kingbinchow]

想迅速生成一个表格。 做了如下操作

create table employee(
employeeId char(8) primary key,
employeeName varchar(20),
gender varchar(6),
birthday date,
address varchar(200),
salary number,
departmentId char(3)
)


create sequence employeeId_seq
start with 1
maxvalue 9999
cache 10;


create or replace procedure employeeInsert_job
as
v_employeeId employee.employeeId%type;
v_employeeName employee.employeeName%type;
v_gender employee.gender%type;
v_birthday employee.birthday%type;
v_address employee.address%type;
v_salary employee.salary%type;
v_departmenId employee.departmentId%type;
v_seqnextval number;

begin
select employeeId_seq.nextval into v_seqnextval from dual;
v_employeeId := trim(to_char(v_seqnextval,'00000000'));
v_employeeName := 'king'||trim(to_char(v_seqnextval,'0000'));
v_birthday := to_date(to_char(to_number(to_char(sysdate,'yyyy-mm-dd'))-v_seqnextval),'yyyymmdd');
v_address := 'NO.'||trim(to_char(v_seqnextval,'0000'))||' LongDongRoad';
v_salary := trunc(dbms_random.value(3300,30000));

----模5分成五个department
if(mod(v_seqnextval,5)= 0)then
v_departmenId := '001';
elsif(mod (v_seqnextval,5) = 1)then
v_departmenId := '002';
elsif(mod (v_seqnextval,5) = 2)then
v_departmenId := '003';
elsif(mod (v_seqnextval,5) = 3)then
v_departmenId := '004';
else
v_departmenId := '002';
end if;

----奇偶数产生性别
if (mod(v_seqnextval,2) = 0)then
v_gender := 'female';
else
v_gender := 'male';
end if;
insert into employee values(v_employeeId,v_employeeName,v_gender,v_birthday,v_address,v_salary,v_departmenId);
commit;
end;



alter system set job_queue_processes = 100;


declare
v_job number;
begin
dbms_job.submit(v_job,'employeeInsert_job();',sysdate,'sysdate + (0.01/(24*60*60))');
commit;
end;

job failed after 17 try.....

[iqlife]

'sysdate + (0.01/(24*60*60))'

？？？

没0.01秒执行一次？

执行的间隔时间要大于存储过程的执行时间

[kingbinchow]

正解。。store procedure的时间是0.14 设为0.2好用 不过源代码里面有个小问题
to_date(to_char(to_number(to_char(sysdate,'yyyy-mm-dd'))-v_seqnextval),'yyyymmdd')
改为
to_date(to_char(to_number(to_char(sysdate,'yyyymmdd'))-v_seqnextval),'yyyymmdd')

[kingbinchow]

代码还有问题还是在
v_birthday :=to_date(to_char(to_number(to_char(sysdate,'yyyymmdd'))-v_seqnextval),'yyyymmdd')
没有考虑mm和dd的限制。。。。汗！！！ 可以直接为sysdate。。。