11 Replies Latest reply: Aug 14, 2014 2:57 PM by Comet RSS

    Job Runs Only Once

    Comet

       

      I am working in an environment with Oracle Database 11g Enterprise Edition Release 11.2.0.2.0

      In SQL Developer 4.0.012 I created a job in the scheduler named GEN_EMAILNOTIFY_PROC_every2hrs with the following parameters

       

      Type of Block: PL/SQL, begin gen_emailnotify_proc end;

      Repeat Interval: FREQ=HOURLY;INTERVAL=2;BYDAY=MON,TUE,WED,THU,FRI

       

      When I set the start date (the end date is null) and enabled to yes the procedure runs once only and then somehow enabled is set to false. 

       

      How do I keep the job enabled?

        • 1. Re: Job Runs Only Once
          GregV

          Hi,

           

          Does your procedure modify the job's attribute? Can you check the following views for your job:

          - all_scheduler_jobs

          - all_scheduler_job_log

          - all_scheduler_job_run_details

          • 2. Re: Job Runs Only Once
            brunovroman

            Hello Comet,

             

            does the job runs successfully? What about running the procedure "manually"? Doesn't it cause an error? What about a test with the procedure code doing nothing (hence no harm), or simply storing a trace of the run in a table (something like the following as body of the procedure: begin insert into job_has_run( moment ) select sysdate from dual; end myproc;).

             

            (my idea is that something goes wrong with the procedure and this disables the job)
            Best regards,

             

            Bruno Vroman.

            • 3. Re: Job Runs Only Once
              Comet

              The last time I ran the job was 08/07/2014 at 7:30 am.  Here are the values in the columns of ALL_SCHEDULER_JOB_LOG for that date.  Notice the ADDITIONAL_INFO column indicates "End time reached."  Is this because, as I stated in the problem statement, that I did not set a value for end-date?  Do I need to set the end-date and is this a limitation in scheduler? 

               

              LOG_ID            1390462

              LOG_DATE      07-AUG-14 07.30.01.744132000 AM -04:00

              OWNER            APEXTDS               

              JOB_NAME                   GEN_EMAILNOTIFY_PROC_every2hrs               

              JOB_SUBNAME                <Null>               

              JOB_CLASS                  DEFAULT_JOB_CLASS               

              OPERATION                  COMPLETED   

              STATUS                     <Null>               

              USER_NAME                  <Null>               

              CLIENT_ID                  <Null>               

              GLOBAL_UID                 VARCHAR2(32)               

              CREDENTIAL_OWNER           <Null>               

              CREDENTIAL_NAME            <Null>               

              DESTINATION_OWNER          <Null>               

              DESTINATION                <Null>               

              ADDITIONAL_INFO        REASON="End time reached"

                

               

              LOG_ID            1390458

              LOG_DATE      07-AUG-14 07.30.01.741361000 AM -04:00

              OWNER            APEXTDS               

              JOB_NAME                   GEN_EMAILNOTIFY_PROC_every2hrs               

              JOB_SUBNAME                <Null>               

              JOB_CLASS                  DEFAULT_JOB_CLASS               

              OPERATION                  RUN   

              STATUS                     SUCCEEDED              

              USER_NAME                  <Null>               

              CLIENT_ID                  <Null>               

              GLOBAL_UID                 VARCHAR2(32)               

              CREDENTIAL_OWNER           <Null>               

              CREDENTIAL_NAME            <Null>               

              DESTINATION_OWNER          <Null>               

              DESTINATION                <Null>               

              ADDITIONAL_INFO            <Null>

              • 4. Re: Job Runs Only Once
                Comet

                Please see my comment to GregV.

                • 5. Re: Job Runs Only Once
                  Comet

                  Yesy I can run it manulayy without any errors.

                  • 6. Re: Job Runs Only Once
                    brunovroman

                    Hello,

                     

                    have you tried with a job that "doesn't do anything" (or that does only a very simple task to show evidence that it has run)?

                    Then I suggest that you open a Service Request (it would help Oracle Support to know if the problem is only with this particular job/procedure or with any job), and please update the post once you know the explanation/solution.

                    Good luck,

                    Bruno.

                    • 7. Re: Job Runs Only Once
                      Comet

                      There is evidence that GEN_EMAILNOTIFY_PROC is run from the job.  It sends an email to two recipients in a list from a table and creates an email body from data in another table.  the problem is that it runs once and then the job is disabled.  I think I will try to end-date in the job at some point far in future. 

                      • 8. Re: Job Runs Only Once
                        GregV

                        Hi,

                         

                        You say that the job runs once and then gets disabled, this looks like the max_runs attribute has been set to 1. That would explain why the job is disabled.

                        Can you make sure this attribute is not set or set it to NULL.

                        • 9. Re: Job Runs Only Once
                          Comet

                          The max_runs value is Null.

                          • 10. Re: Job Runs Only Once
                            brunovroman

                            I will repeat myself for the third (and last time, don't worry): have you tried to define another job that is pretty much "innocent of anything": a procedure that would do absolutely nothing, just to check if you can define a job that runs more than once... If it succeeds, you have to find the difference between your job and this one... Another test might be to redefine your procedure so that it doesn't do anything anymore, and check that the job still gets disabled after one run (if it doesn't get disabled, the "guilty" is to be found inside the procedure, if it get disable, inside the job or scheduler parameters).

                            Best regards,

                            Bruno.

                            • 11. Re: Job Runs Only Once
                              Comet

                              Bruno, Cool your jets :-).  I created a proc that inserts a row in a log table.  So far the job runs without getting disabled.  I added another line to the proc that issues the send email procedure and I am waiting for it to launch.  I think the email procedure is where the problem is.