Toad World® Forums

Issue with job scheduler tool


SQL Nav ver

When using the job schedule tool, we’ve noticed that almost anything we do to a job causes the job to be immediately submitted. Most of our jobs are set up with a repeat interval specified with a pl/sql function, and we are trying to change them to use scheduler calendaring. However, until that change gets made, simply disableing the job causes the scheduler to recalculate the NEXT_RUN_DATE and thus submit the job immediately.

I finally did a session trace to see what was going on with this. The steps I followed are:
1 - start SQL Nav and establish a new session against the database
2 - star the scheduler tool
3 - select a job
4 - uncheck the ‘enabled’ box
5 - from a seperate sqlplus session, start a trace on the sql nav session (exec DBMS_MONITOR.SESSION_TRACE_ENABLE …)
6 - back in SQL Nav, hit the ‘save’ button to execute the pending ‘disable’ action
7 - back in the sqlplus session, stop the trace
8 - review the raw trace file

What the trace revealed was that SQLNav is doing a whole bunch of stuff unrelated to simply disabling a job. Here’s a brief of what I see in the trace file

begin sys.dbms_scheduler.set_attribute(:name,:attr,:value); end; (job_class)
begin sys.dbms_scheduler.get_attribute(:name,:attr,:result); end; (program_name)
begin sys.dbms_scheduler.get_attribute(:name,:attr,:result); end; (schedule_name)
begin sys.dbms_scheduler.get_attribute(:name,:attr,:result); end;(job_type)
begin sys.dbms_scheduler.set_attribute(:name,:attr,:value); end;(job_action)
begin sys.dbms_scheduler.set_attribute(:name,:attr,:value); end;(job_type)
begin sys.dbms_scheduler.set_attribute(:name,:attr,false); end;(auto_drop)
begin sys.dbms_scheduler.set_attribute_null(:name,:attr); end;(end_date)
begin sys.dbms_scheduler.set_attribute(:name,:attr,:value); end;(repeat_interval)
begin sys.dbms_scheduler.set_attribute(:name,:attr,:value); end;(comments)

And finally, the one and only thing I specified to be done …

begin sys.dbms_scheduler.disable(:name); end;()

So my question is … why is SQLNav doing all the extra, unnecessary calls to dbms_scheduler?


Hi estevens_062,

Currently the logic of the job saving routine is too simple: instead of setting only the attributes that have changed, we set ALL attributes displayed to the user. This certainly should be improved. I don’t understand though why your jobs are submitted after you save them. I’ll investigate and come back to you.



thanks for the reply. As for the unplanned job submission, my tracing did not show them being specifically submitted, so I presume it has to do with the nature of the pl/sql functions used to specify the repeat interval. These are old, home-grown functions originally written for dbms_job instead of dbms_scheduler. I really don’t have a good handle on that, and replacing them with dbms_scheduler calandering is a big part of this current project. I am presuming that something about the changes causes the scheduler to recalculate NEXT_RUN_DATE and somehow come up with “now”. I have not observed this behavior when REPEAT_INTERVAL is expressed as a calender rule instead of a pl/sql function.