dbms_job.submit doesn't work ?
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
insert into test values (1, 'asdf');
insert into test values (2, 'lalalalla');
--procedure that should update the 2 dummy records in my test table
create or replace procedure testjob is
update test set description ='update';
when others then
raise_application_error(-20001, 'Procedure testjob caused an ERROR!! Message: '||sqlerrm);
--submit the job and hope something happens
set serveroutput on;
dbms_job.submit ( job => v_jobno,
what => 'testjob;',
next_date => sysdate + numtodsinterval(5,'minute')
dbms_output.put_line('Jobnumber: ' || v_jobno);
Check your alert log for errors.
You should try to set your what parameter as:
what => 'begin testjob; end;',
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?
well where in the submit job did you put the interval (you didnt)
Check the "job_queue_processes" parameter. It must be set to value greater than zero.
The whole difference between a little boy and an adult man is the price of toys
Click Here to Expand Forum to Full Width