dbms_job.submit doesn't work ?

[souljumper]

hi

i tried to get an example running that uses dbms_job.submit to execute a procedure every 5 minutes.

i created a test-table which contains 2 records, my procedure should update one column of every record.

so far i see, the job is submited buy my procedure doesn't seemed to be executed.

may be someone can see my mistake...i dont get it

-- create test table with 2 dummy records
drop table test;
create table test
(
oid integer,
description varchar2(50)
);
insert into test values (1, 'asdf');
insert into test values (2, 'lalalalla');
commit;

--procedure that should update the 2 dummy records in my test table
create or replace procedure testjob is
begin  
 update test set description ='update';   
 commit;
 exception
  when others then
       raise_application_error(-20001, 'Procedure testjob caused an ERROR!! Message: '||sqlerrm);
end;
 /
show errors; 

--submit the job and hope something happens
set serveroutput on; 
declare
v_jobno binary_integer;
begin  

dbms_job.submit ( job        =>  v_jobno,              
                  what       =>  'testjob;',  
                  next_date  =>  sysdate + numtodsinterval(5,'minute')
                );
   dbms_output.put_line('Jobnumber: ' || v_jobno);
  commit;
end;
/
show errors

[davey23uk]

did you commit it

[ebrian]

Check your alert log for errors.

You should try to set your what parameter as:

what => 'begin testjob; end;',

[souljumper]

how can i view the alert.log ?

i commited the submit, but that didn't help. i found the command dbms_job.run("job-id").

if i call this command with my just created job id my job gets executed, but my interval still doesn't work. the job don't run again after the time elapsed.

i took a look in the user_jobs table and found my job(s) the "broken" flag is set to 'N' what (how i suppose) means "no". the interval time seems to be correct aswell.

any idea why the submit doesn't start automatically and why the job don't get started again?

[davey23uk]

well where in the submit job did you put the interval (you didnt)

[ales]

Check the "job_queue_processes" parameter. It must be set to value greater than zero.