Home » SQL & PL/SQL » SQL & PL/SQL » Scheduled job is not running automatically (merged)
Scheduled job is not running automatically (merged) [message #403509] |
Sat, 16 May 2009 01:43  |
 |
delna.sexy
Messages: 941 Registered: December 2008 Location: Surat, The Diamond City
|
Senior Member |
|
|
Hi all,
In previous post I was having problem with the ARGUMENTs of the PROGRAM. And I solved it by trial and error.
Now JOB and PROGRAM is created using DBMS_SCHEDULER package.
But problem here is that JOB is not running on the scheduled time automatically. If I run the JOB by RUN_JOB method then only it gets executed.
following is the output of all_scheduler_jobs columns having NOT NULL.
SQL>select owner, job_name, job_style, job_creator, client_id,
2 program_owner, program_name, schedule_type, start_date,
3 job_class, enabled, auto_drop, restartable, state,
4 job_priority, run_count, failure_count, retry_count,
5 next_run_date, logging_level, stop_on_window_close,
6 instance_stickiness, system, job_weight, deferred_drop
7 from all_scheduler_jobs
8 where job_name = 'JOB_DIR_VERIFY37';
OWNER |JOB_NAME |JOB_STYLE
==============================|==============================|===========
JOB_CREATOR |CLIENT_ID
==============================|================================================================
PROGRAM_OWNER
====================================================================================================
PROGRAM_NAME
====================================================================================================
SCHEDULE_TYP|START_DATE
============|===========================================================================
JOB_CLASS |ENABL|AUTO_|RESTA|STATE |JOB_PRIORITY| RUN_COUNT
==============================|=====|=====|=====|===============|============|==========
FAILURE_COUNT|RETRY_COUNT
=============|===========
NEXT_RUN_DATE |LOGGING_LEV|STOP_|INSTA
===========================================================================|===========|=====|=====
SYSTE|JOB_WEIGHT|DEFER
=====|==========|=====
XLNTSEO |JOB_DIR_VERIFY37 |REGULAR
APEX_PUBLIC_USER |NARESH:619712768328050
XLNTSEO
PRG_DIR_VERIFY37
ONCE |16-MAY-09 11.57.47.000000 AM +05:30
DEFAULT_JOB_CLASS |TRUE |TRUE |FALSE|SCHEDULED | 3| 0
0| 0
16-MAY-09 11.57.47.000000 AM +05:30 |OFF |FALSE|TRUE
FALSE| 1|FALSE
1 row selected.
And following is the all_scheduler_programs
SQL>select owner, program_name, program_type, program_action,
2 number_of_arguments, enabled, detached, priority, weight
3 from all_scheduler_programs
4 where program_name = 'PRG_DIR_VERIFY37';
OWNER |PROGRAM_NAME |PROGRAM_TYPE
==============================|==============================|================
PROGRAM_ACTION
====================================================================================================
NUMBER_OF_ARGUMENTS|ENABL|DETAC| PRIORITY| WEIGHT
===================|=====|=====|==========|==========
XLNTSEO |PRG_DIR_VERIFY37 |STORED_PROCEDURE
SEO_VERIFICATION.VERIFY_DIR_SUBMISSION
5|TRUE |FALSE| 3| 1
1 row selected.
By seeing this fact, can anyone tell me that what is the problem with it in executing?
Thanks in advance.
|
|
|
|
Re: Scheduled job is not running automatically (merged) [message #404007 is a reply to message #403539] |
Wed, 20 May 2009 01:01   |
 |
delna.sexy
Messages: 941 Registered: December 2008 Location: Surat, The Diamond City
|
Senior Member |
|
|
Thank you Kevin sir for your concern.
Although I put COMMIT statement problem remains as it is.
Please have a look at following code if you can find any problem.
Following is the snippet of the code to create JOB.
...
v_prog_name := 'Prg_DIR_VERIFY' || v_seq_value;
81 v_job_name := 'Job_DIR_VERIFY' || v_seq_value;
82 DBMS_SCHEDULER.CREATE_PROGRAM( program_name => v_prog_name,
83 program_type => 'STORED_PROCEDURE',
84 program_action=>'SEO_VERIFICATION.VERIFY_DIR_SUBMISSION',
85 number_of_arguments => 5,
86 enabled => FALSE,
87 comments => NULL);
88 DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(program_name => v_prog_name,
89 argument_position => 1,
90 argument_name => 'P_CLIENT_I',
91 argument_type => 'NUMBER',
92 default_value => P2005_CLIENT,
93 out_argument => FALSE);
94 DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(program_name => v_prog_name,
95 argument_position => 2,
96 argument_name => 'P_WEBSITE_I',
97 argument_type => 'NUMBER',
98 default_value => P2005_WEBSITE,
99 out_argument => FALSE);
100 DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(program_name => v_prog_name,
101 argument_position => 3,
102 argument_name => 'P_STATUS_I',
103 argument_type => 'NUMBER',
104 default_value => P2005_STATUS,
105 out_argument => FALSE);
106 DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(program_name => v_prog_name,
107 argument_position => 4,
108 argument_name => 'P_SCHEDULE_I',
109 argument_type => 'NUMBER',
110 default_value => v_schedule_pk,
111 out_argument => FALSE);
112 DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(program_name => v_prog_name,
113 argument_position => 5,
114 argument_name => 'P_PROGRAM_NAME_I',
115 argument_type => 'VARCHAR2',
116 default_value => v_prog_name,
117 out_argument => FALSE);
118 DBMS_SCHEDULER.CREATE_JOB( job_name => v_job_name,
119 program_name => v_prog_name,
120 start_date => SYSTIMESTAMP+0.0035,
121 repeat_interval => NULL,
122 end_date => NULL,
123 job_class => 'DEFAULT_JOB_CLASS',
124 enabled => FALSE,
125 auto_drop => TRUE,
126 comments => NULL);
127 COMMIT;
128 DBMS_SCHEDULER.ENABLE(v_prog_name);
129 DBMS_SCHEDULER.ENABLE(v_job_name);
130 commit;
...
and following are the details of JOB and PROGRAM attributes.
SQL>select owner, job_name, job_style, job_creator, client_id,
2 program_owner, program_name, schedule_type, start_date,
3 job_class, enabled, auto_drop, restartable, state,
4 job_priority, run_count, failure_count, retry_count,
5 next_run_date, logging_level, stop_on_window_close,
6 instance_stickiness, system, job_weight, deferred_drop
7 from all_scheduler_jobs
8 where job_name = 'JOB_DIR_VERIFY88';
OWNER |JOB_NAME |JOB_STYLE |JOB_CREATOR |CLIENT_ID
==============================|==============================|===========|==============================|================================================================
PROGRAM_OWNER
========================================================================================================================================================================================================
PROGRAM_NAME
========================================================================================================================================================================================================
SCHEDULE_TYP|START_DATE |JOB_CLASS |ENABL|AUTO_|RESTA|STATE |JOB_PRIORITY| RUN_COUNT|FAILURE_COUNT
============|===========================================================================|==============================|=====|=====|=====|===============|============|==========|=============
RETRY_COUNT|NEXT_RUN_DATE |LOGGING_LEV|STOP_|INSTA|SYSTE|JOB_WEIGHT|DEFER
===========|===========================================================================|===========|=====|=====|=====|==========|=====
XLNTSEO |JOB_DIR_VERIFY88 |REGULAR |XLNTSEO |
XLNTSEO
PRG_DIR_VERIFY88
ONCE |20-MAY-09 11.16.57.000000 AM +05:30 |DEFAULT_JOB_CLASS |TRUE |TRUE |FALSE|SCHEDULED | 3| 0| 0
0|20-MAY-09 11.16.57.000000 AM +05:30 |OFF |FALSE|TRUE |FALSE| 1|FALSE
1 row selected.
SQL>select owner, program_name, program_type, program_action,
2 number_of_arguments, enabled, detached, priority, weight
3 from all_scheduler_programs
4 where program_name = 'PRG_DIR_VERIFY88';
OWNER |PROGRAM_NAME |PROGRAM_TYPE
==============================|==============================|================
PROGRAM_ACTION
========================================================================================================================================================================================================
NUMBER_OF_ARGUMENTS|ENABL|DETAC| PRIORITY| WEIGHT
===================|=====|=====|==========|==========
XLNTSEO |PRG_DIR_VERIFY88 |STORED_PROCEDURE
SEO_VERIFICATION.VERIFY_DIR_SUBMISSION
5|TRUE |FALSE| 3| 1
1 row selected.
as said earlier, JOB is not being executed on its scheduled time and that is the problem.
Please help me in this regard.
regards,
Delna
|
|
|
Re: Scheduled job is not running automatically (merged) [message #406450 is a reply to message #404007] |
Thu, 04 June 2009 01:47  |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Kevin,
This is dbms_scheduler, not dbms_job. A lot has changed.
Delna,
Is the problem that the job is not running at all or that it is not running when you expect it to? When are you expecting it to run? You have it set to run only once a few seconds after it is enabled or as soon after that as it can with no retries in case of problems and automatically dropped and not repeated.
|
|
|
Goto Forum:
Current Time: Sun Feb 09 21:15:39 CST 2025
|