-
dbms_job.submit doesn't work ?
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
-
-
Check your alert log for errors.
You should try to set your what parameter as:
Code:
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.
Ales The whole difference between a little boy and an adult man is the price of toys
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|