Submit a job to run every day.
573274May 10 2010 — edited Jul 6 2010Hi,
I am using Oracle-10g DB. I have a procedure that takes 2 dates as in parameters and inserts the summary of transactions for that interval of dates provided in the parameter into a table. I used the PL/SQL block to call dbms_job.submit. It is calling the procedure for the first time and not calling it the next day on. Please let me know what needs to be done so that it calls the procedure everyday at 6.45 P.M. and inserts the summary of transactions into the table.
DBMS_JOB.submit call below:
declare
my_job number:=0;
v_err varchar2(2000);
begin
my_job:=my_job+1;
dbms_job.submit(job => my_job,
what => 'Daily_TRAN(sysdate-1,sysdate);',
next_date => trunc(sysdate)+18.75/24,
interval => 'trunc(sysdate)+1+18.75/24');
Insert into PL_Except values (' Daily Sum Call Job_Scheduler-007 block Successful. ',sysdate);
commit;
exception
when others then
v_err:=sqlerrm;
Insert into PL_Except values (v_err||' Raised at Daily Sum Call Job_Scheduler-007 block: ',sysdate);
commit;
end;
/