Some Scheduler oddities in TOAD

  1. When changing the CLASS of a scheduler job (and I think that it is
    more than just CLASS), SQL is generated for a whole bunch of other
    changes. I saw six different statements generated from 1 change, and
    (from my knowledge of scheduler) that shouldn’t happen.

  2. The editor parses the statement when it is an executable. ie.
    ‘/usr/local/bin/run_this.sh’ gets parsed and saved to
    ‘/usr/LOCAL/bin/run_this.sh’. No good. Executables shouldn’t be parsed.

David
utahtoad at gmail dot com

Hi David,

I agree on #2 – the path names should be excluded no matter if the slash
is forward (Unix/Linux) or backwards (Windows). Even though the latter is not
case sensitive – paths should be excluded. Because someday user may want
to run the same script on a different platform J

Don’t get me wrong, Toad’s Scheduler Interface is better than OEM, but it still
needs some strengthning.
Ok, I’d better look a little closer, so I can give you exact instances and
steps, but see another problem.

Another issue…

  1. Adding a “Specify Schedule Info” End date produces a bunch of statements,
    many of which shouldn’t be needed to be touched. (See below)
    I think that if it isn’t touched, don’t change it…or “clear things out”. As
    you can see, an error is produced because it is trying to change schedule_name,
    and I didn’t change schedule_name and it didn’t have a schedule_name to begin
    with.
    SQL> BEGIN
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name => ‘PARTNER.FUEL_REBATE_PURCHASE_FILE’
    ,attribute => ‘SCHEDULE_NAME’);
    END;
    BEGIN
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name => ‘PARTNER.FUEL_REBATE_PURCHASE_FILE’
    ,attribute => ‘SCHEDULE_NAME’);
    END;
    Error at line 2
    ORA-27470: failed to re-enable “PARTNER.FUEL_REBATE_PURCHASE_FILE” after making requested change
    ORA-27481: “PARTNER.FUEL_REBATE_PURCHASE_FILE” has an invalid schedule
    ORA-06512: at “SYS.DBMS_ISCHED”, line 2834
    ORA-06512: at “SYS.DBMS_SCHEDULER”, line 1847
    ORA-06512: at “SYS.DBMS_SCHEDULER”, line 1999
    ORA-06512: at line 2

SQL> BEGIN
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => ‘PARTNER.FUEL_REBATE_PURCHASE_FILE’
,attribute => ‘REPEAT_INTERVAL’);
END;
PL/SQL procedure successfully completed.
SQL> BEGIN
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => ‘PARTNER.FUEL_REBATE_PURCHASE_FILE’
,attribute => ‘EVENT_SPEC’);
END;
PL/SQL procedure successfully completed.
SQL> BEGIN
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => ‘PARTNER.FUEL_REBATE_PURCHASE_FILE’
,attribute => ‘START_DATE’);
END;
PL/SQL procedure successfully completed.
SQL> BEGIN
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => ‘PARTNER.FUEL_REBATE_PURCHASE_FILE’
,attribute => ‘END_DATE’);
END;
PL/SQL procedure successfully completed.
SQL> BEGIN
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => ‘PARTNER.FUEL_REBATE_PURCHASE_FILE’
,attribute => ‘START_DATE’
,value => TO_TIMESTAMP_TZ(‘2009/12/10 05:00:00.000000 MST7MDT’,‘yyyy/mm/dd hh24:mi:ss.ff tzr’));
END;
PL/SQL procedure successfully completed.
SQL> BEGIN
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => ‘PARTNER.FUEL_REBATE_PURCHASE_FILE’
,attribute => ‘END_DATE’
,value => TO_TIMESTAMP_TZ(‘2010/07/05 12:00:00.000000 -06:00’,‘yyyy/mm/dd hh24:mi:ss.ff tzr’));
END;
PL/SQL procedure successfully completed.
SQL> BEGIN
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => ‘PARTNER.FUEL_REBATE_PURCHASE_FILE’
,attribute => ‘REPEAT_INTERVAL’
,value => ‘FREQ=DAILY;INTERVAL=1;byhour=5;byminute=0;bysecond=0’);
END;
PL/SQL procedure successfully completed.
SQL> BEGIN
SYS.DBMS_SCHEDULER.ENABLE
(name => ‘PARTNER.FUEL_REBATE_PURCHASE_FILE’);
END;
PL/SQL procedure successfully completed.


David A. Hicken
UtahToad at gmail dot com
---------- Kiva.org - Make a Small Loan. Make a Big difference.
We are apt to forget that a great man is thus not only great, but also a man: that a philosopher, in a life time, spends less hours pondering the destiny of the race than he gives over to wondering if it will rain tomorrow and to meditating upon the toughness of steaks.
– H.L. Mencken from The Philosophy of Friedrich Nietzsche

John Dorlon wrote:

Hi David,

  1. I just tried this here and for me the only thing that changed was the
    class. If you touched anything having to do with the schedule, it does
    ‘clear things out’ before resetting them. It should be harmless.
    If you find that’s not the case, please let me know

    1. No comment. This one is for another developer.

-John

I’m feeling lonely. Did anyone not see this? There was no response!

DAH

David,

Maybe you need a pet. Or maybe I need an assistant. J

So it didn’t have a schedule to begin with? I wonder where ‘
partner.fuel …’ come from then. Is there a job by the same name in
another schema or something like that?

-John

The PARTNER.FUEL_… is the name of the Scheduler job, but there is no named
schedule. when it tries to null out the named schedule is when you get the
error. I think this is a case of “clearing things out” that don’t need cleared,
hence an error is thrown. It’s okay if you ignore it, but getting errors is
still a pain.
Almost all of the SQL statements are “clearing things out” that don’t need
cleared.

David A. Hicken
UtahToad at gmail dot com
---------- Kiva.org - Make a Small Loan. Make a Big difference.
Patience is not passive: on the contrary it is active; it is concentrated strength.

John Dorlon wrote:

David,

Maybe you need a pet. Or maybe I need an assistant. J

So it didn’t have a schedule to begin with? I wonder where ‘
partner.fuel …’ come from then. Is there a job by the same name
in another schema or something like that?

-John

Hi David,

I just realized that this is not the beta group. What version of Toad are you
on? I tried it in the beta and in 10.5, but when I changed job class, the only
thing that the script changed was job class.

-John

Toad version 10.5.1.3
Oracle version 10.2.0.2

I’ll admit that it seems I don’t get that every time. I work in scheduler a lot,
so I see it enough. We have changed all of our jobs and cron jobs to scheduler.
It has been an interesting journey, but worth it. We use time-reliant processes
for a lot of the things we do.

David A. Hicken
UtahToad at gmail dot com
---------- Kiva.org - Make a Small Loan. Make a Big difference.
We cannot do everything at once, but we can do something at once.
–Calvin Coolidge

John Dorlon wrote:

Hi David,

I just realized that this is not the beta group. What version of Toad are
you on? I tried it in the beta and in 10.5, but when I changed job class,
the only thing that the script changed was job class.

-John

Looking at the source, I see that if any of these items are changed in the alter
scheduler job window… event queue owner, name, or agent schedule owner or
name Start Date, End Date, or Interval. …then we will clear out all of the
following attributes before coming back in and setting just the new settings:
SCHEDULER_NAME, REPEAT_INTERVAL, EVENT_SPEC, START_DATE, END_DATE The reason we
‘clear things out’ is that, certain changes would cause errors due
to conflicts with existing settings (IIRC, one example was changing from a
predefined schedule to one defined on the job itself). Clearing out all
scheduling props and then setting the new values prevented the error. Partway
through the script, you get “ORA-27470: failed to re-enable … after
making requested change”. Is the only problem the error itself? I mean, if
you ignore the error, is everything as it should be when the script completes?
Maybe the solution would be to trap and ignore ORA-27470 while clearing the
settings, and make sure that the job’s enablement is correct at the end of
the script.

That seems to be the only type of error that I get when making scheduler
changes, whether it be CLASS or SCHEDULE. Maybe to catch and trap that error
and make sure that enabled jobs are enabled when done, or disabled jobs are
left disabled may be appropriate. – David A. Hicken UtahToad at gmail dot
com ---------- Kiva.org - Make a Small Loan. Make a Big difference. A friend
is someone who makes me feel totally acceptable. – Ene Riisna John Dorlon
wrote: Looking at the source, I see that if any of these items are changed
in the alter scheduler job window… event queue owner, name, or agent
schedule owner or name Start Date, End Date, or Interval. …then we will
clear out all of the following attributes before coming back in and setting
just the new settings: SCHEDULER_NAME, REPEAT_INTERVAL, EVENT_SPEC,
START_DATE, END_DATE The reason we ‘clear things out’ is that,
certain changes would cause errors due to conflicts with existing settings
(IIRC, one example was changing from a predefined schedule to one defined on
the job itself). Clearing out all scheduling props and then setting the new
values prevented the error. Partway through the script, you get
“ORA-27470: failed to re-enable … after making requested
change”. Is the only problem the error itself? I mean, if you ignore
the error, is everything as it should be when the script completes? Maybe
the solution would be to trap and ignore ORA-27470 while clearing the
settings, and make sure that the job’s enablement is correct at the
end of the script.

I just found out why this was happening. Some scheduler jobs have a start_date
in dba_scheduler_jobs even when using a named schedule (not one defined in the
job). The alter job window was only populating the start date for jobs with
their own defined schedule. So the comparison code was noting that as a schedule
difference.

It should be fixed next beta.