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
Code:
-- 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