Alter Job bug in 10.0GA?

Toad for Oracle
1

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

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

2

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>–

3

Hi Martin,

I think you have found a bug !
:slight_smile:

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! :wink:

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

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

4

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]

5

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

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

6

I see your messages, Norm and I’ll take a look at it soon – just
buried in something else right now…

7

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

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