Issue with job scheduler tool

SQL Navigator
1

SQL Nav ver 6.7.0.2372

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?

2

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.

Regards,
Roman

3

Roman,
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.