dbms_job

[ramadas]

Guy's help me out here....
I wanted to do some time based chnaged in database table so i wrote a procedure which does that work...I have checked the procedure separately, it works properly.
Then i had written this code to execute the prodedure at regular (specified interval):
DECLARE
jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT
(job => jobno,what => 'begin proc_accno; end;',
next_date => SYSDATE,interval => 'SYSDATE+1/24');
COMMIT;
END;

Can anybody tell me why is this not working properly..i mean that why is that the procedure named proc_accno is not executed after the inetrval specified is crossed.Where have i gone wrong...please guide me...

thanks
ram

[Highlander]

Try this:

DECLARE jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT(jobno, 'proc_accno();',
SYSDATE,
'SYSDATE + 1/24');
COMMIT;
END;

[kmesser]

Two things:

1. You must have job_queue_interval specificed in your init.ora file at a frequency greater than your job frequency.

2. You must put a COMMIT as the next statement.

Good luck!

[sasmita]

If still ur job is not working properly
Addition to kmesser's solution..

What you need to do is amend the init file for the database and add/amend the following lines to read as below

JOB_QUEUE_PROCESSES = 10
JOB_QUEUE_INTERVAL = 1

Once these have been changed, you will have to bounce the database.


All the best