# Alter Job bug in 10.0GA? **URL:** \ **Category:** Toad for Oracle & DevOps Toolkit\ **Created:** [April 23, 2010, 5:31am UTC](https://forums.toadworld.com/t/alter-job-bug-in-10-0ga/9445 "2010-04-23T05:31:32Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![NormTeamT](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/normteamt/32/53_2.png) [@NormTeamT](https://forums.toadworld.com/u/NormTeamT)\ **Post date:** [April 23, 2010, 5:31am UTC](https://forums.toadworld.com/t/alter-job-bug-in-10-0ga/9445/1 "2010-04-23T05:31:32Z")
Morning all, I’m connected to an 8i database and altering a job that is scheduled to run next week. I want to change ONLY the interval from “TRUNC(SYSDATE+1)+7.5/24” to “TRUNC(SYSDATE+1)+8.5/24” (ignore the quotes). I have not adjusted the Next run date and time. The SQL generated always seems to change the next date as well as the interval: BEGIN SYS.DBMS\_IJOB.WHAT (job =\> 37 ,what =\> ‘dbms\_refresh.refresh(’’“FDMSMDRO”.“FDMSMDGRP”’’);’); SYS.DBMS\_IJOB.NEXT\_DATE (job =\> 37 ,next\_date =\> TRUNC(SYSDATE+1)+8.5/24); SYS.DBMS\_IJOB.INTERVAL (job =\> 37 ,interval =\> ‘TRUNC(SYSDATE+1)+8.5/24’); END; In order to prevent this from happening, I have to explicitly set the next date and time to the same setting by simply clicking on the ‘specify’ radio button. The resulting SQL is now: BEGIN SYS.DBMS\_IJOB.WHAT (job =\> 37 ,what =\> ‘dbms\_refresh.refresh(’’“FDMSMDRO”.“FDMSMDGRP”’’);’); SYS.DBMS\_IJOB.NEXT\_DATE (job =\> 37 ,next\_date =\> to\_date(‘01/01/4000 00:00:00’,‘mm/dd/yyyy hh24:mi:ss’)); SYS.DBMS\_IJOB.INTERVAL (job =\> 37 ,interval =\> ‘TRUNC(SYSDATE+1)+8.5/24’); END; (Yes, I know this job is not set to run until 01/01/4000 but had I not checked the SQL, alter job would have set it to go tomorrow at 08:30 - not what I wanted. I notice that the WHAT is changed as well - I’m thinking that the SQL generated should only be that for any changes I make - so when I change the interval, the SQL should (?) be: BEGIN SYS.DBMS\_IJOB.INTERVAL (job =\> 37 ,interval =\> ‘TRUNC(SYSDATE+1)+8.5/24’); END; Cheers, Norm. [TeamT] Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at [www.environment-agency.gov.uk](http://www.environment-agency.gov.uk) Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at [www.environment-agency.gov.uk](http://www.environment-agency.gov.uk) ---
**Author:** ![MGainty](https://avatars.discourse-cdn.com/v4/letter/m/a5b964/32.png) [@MGainty](https://forums.toadworld.com/u/MGainty)\ **Post date:** [April 23, 2010, 8:56am UTC](https://forums.toadworld.com/t/alter-job-bug-in-10-0ga/9445/2 "2010-04-23T08:56:21Z")
good afternoon norm i think you have found a bug ! sqlplus\>set serveroutput on sqlplus\>variable jobno number; sqlplus\>variable instno number; sqlplus\>begin sqlplus\>select instance\_number into :instno from v$instance; sqlplus\>– ---
**Author:** ![NormTeamT](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/normteamt/32/53_2.png) [@NormTeamT](https://forums.toadworld.com/u/NormTeamT)\ **Post date:** [April 23, 2010, 9:09am UTC](https://forums.toadworld.com/t/alter-job-bug-in-10-0ga/9445/3 "2010-04-23T09:09:23Z")
Hi Martin, > > I think you have found a bug ! > > 🙂 > > how should this bug be handled? > > Well, what I've done is to either: - choose to specify a next date and leave it as it currently is, that correctly sets the next date bit of SQL; or - show SQL and send to editor, then delete the bits I don't need - leaving only the change to the interval. I suppose there's another option, don't ALTER JOB! 😉 Cheers, Norm. [TeamT] Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at [www.environment-agency.gov.uk](http://www.environment-agency.gov.uk) Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at [www.environment-agency.gov.uk](http://www.environment-agency.gov.uk) ---
**Author:** ![MGainty](https://avatars.discourse-cdn.com/v4/letter/m/a5b964/32.png) [@MGainty](https://forums.toadworld.com/u/MGainty)\ **Post date:** [April 23, 2010, 9:28am UTC](https://forums.toadworld.com/t/alter-job-bug-in-10-0ga/9445/4 "2010-04-23T09:28:01Z")
from the test-scripts i’ve run in sqlplus…i think this maybe a larry bug and not toad…i’ll do the “politically correct thing” and let you handle it thanks norm! Martin Gainty * * * [standard caveats go here] ---
**Author:** ![NormTeamT](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/normteamt/32/53_2.png) [@NormTeamT](https://forums.toadworld.com/u/NormTeamT)\ **Post date:** [April 23, 2010, 9:53am UTC](https://forums.toadworld.com/t/alter-job-bug-in-10-0ga/9445/5 "2010-04-23T09:53:47Z")
Hi Martin, > > from the test-scripts i've run in sqlplus..i think this > > maybe a larry bug and not toad..i'll do the "politically > > correct thing" and let you handle it I don't think so. Toad is generating the wrong SQL for an ALTER JOB: If I choose ONLY to alter the interval and specifically leave the NEXT\_DATE untouched - it shows in the current NEXT\_DATE setting, Toad sets up a anonymous block to : - Set the WHAT to WHAT it currently is. (Not really a problem, but extra un-needed processing). - Set the NEXT\_DATE to the NEW Interval. (A major problem!) \*\*\* SEE BELOW FOR UPDATE! - Set the INTERVAL to the new Interval. (Exactly what I want.) What (I think) it should be doing is: - Set the INTERVAL to the new Interval. And nothing else. In order to keep the NEXT\_DATE unchanged, I have to select the SPECIFY radio button and that's all. If I do this, the generated SQL correctly sets the NEXT\_DATE to the current NEXT\_DATE which is additional work that is not needed - as I'm not changing the date (if you see what I mean?). I think the best processing is as follows: If WHAT (What to execute) is changed, generate code to update WHAT for the job. If NEXT DATE (Next Execution) has changed, generate code to update NEXT\_DATE for the job. If INTERVAL (Subsequent Executions) has changed, generate code to update INTERVAL for the job. \*\*\* OK, I'm an idiot, it's Friday and I haven't slept properly for weeks. (Those are my excuses and I'm holding on to them!) There is a defaulted radio button that says, next to "Next Execution" to "Use Expression for subsequent executions" and it is that which causes the next date to be set to the same as the interval. My bad for not spotting that. However, I think we need a radio button option that says "leave next execution date unchanged" - which should be the default. Cheers, Norm. [TeamT] Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at [www.environment-agency.gov.uk](http://www.environment-agency.gov.uk) Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at [www.environment-agency.gov.uk](http://www.environment-agency.gov.uk) ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [April 23, 2010, 9:58am UTC](https://forums.toadworld.com/t/alter-job-bug-in-10-0ga/9445/6 "2010-04-23T09:58:43Z")
I see your messages, Norm and I’ll take a look at it soon – just buried in something else right now… ---
**Author:** ![NormTeamT](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/normteamt/32/53_2.png) [@NormTeamT](https://forums.toadworld.com/u/NormTeamT)\ **Post date:** [April 23, 2010, 10:07am UTC](https://forums.toadworld.com/t/alter-job-bug-in-10-0ga/9445/7 "2010-04-23T10:07:59Z")
Hi John, > > I see your messages, Norm and I'll take a look at it soon - > > just buried in something else right now... > > Yes, no worries John. There's no rush - today was the first time I have > > had to use the ALTER JOB feature in years of working with > > databases. I'm writing QT C++ code today! It's all going horribly wrong! Cheers, Norm. [TeamT] Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at [www.environment-agency.gov.uk](http://www.environment-agency.gov.uk) Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at [www.environment-agency.gov.uk](http://www.environment-agency.gov.uk)