I am working on Oracle 10g database. I am trying to run a procedure everyday as a schedule to be run at 6.45 PM. I used the anonymous block below as suggested in one of the replies to my earlier thread. It executes the script first time around 6:45 PM and the next day onwards it executes at midnight. This is resulting in some duplicate records being inserted. How do I make it run every day exactly at 6:45 PM? The code needs to go to production soon and appreciate an early response.
dbms_job.submit(job => my_job,
what => 'Daily_TRAN(sysdate-140,sysdate-139);',
next_date => trunc(sysdate)+1+(18*60+45)/(24*60),
interval => 'trunc(sysdate)+1');
I am not using sys user. Basically, my schedule runs fine. But it does not run at exactly 6.45 PM every night. After the first run, it runs at midnight everyday which results in duplicate rows being inserted.
I checked an example and tried to schedule the job using scheduler and I got the below error message.
job_name => 'Insert_Transactions',
job_type => 'STORED_PROCEDURE',
job_action => 'Daily_TRAN(sysdate-1,sysdate)',
start_date => '07-JUL-10 02.30.00 PM',
repeat_interval => 'FREQ=DAILY;INTERVAL=1', /* every other day */
--end_date => '20-NOV-04 07.00.00 PM Australia/Sydney',
job_class => 'Daily_trans_Insert',
comments => 'My new job');
Error at line 1
ORA-27452: Daily_TRAN((sysdate-1,sysdate) is an invalid name for a database object.
ORA-06512: at "SYS.DBMS_ISCHED", line 99
ORA-06512: at "SYS.DBMS_SCHEDULER", line 262
ORA-06512: at line 2
I have permission as I have DBA and I also have sys user in the test environment. There is not much difference between your method and my method that I posted on my initial thread. It runs fine the first time it executes at the time specified. But the problem is with the interval: interval => 'TRUNC(SYSDATE+1)'. This makes the script run everyday at midnight and sometimes I am getting the rows inserted twice and making the rows duplicate. Today I scheduled as per the suggestion posted by someone else to set the interval as: interval => 'trunc(sysdate)+ 1 + (18*60+45)/(24*60)');. I am hoping that it will work.