2 Replies Latest reply on May 3, 2012 1:34 AM by 655359

    Coding a job in PL/SQL to handle being stopped gracefully

      Hi all,

      I'm using Oracle on Solaris (SunOS 5.10).

      I'd like to know if there is any way I can code my PL/SQL job to "handle" a call from DBMS_SCHEDULER.STOP_JOB properly.

      Is there an API available or some other mechanism I can call on to handle interrupts or whatever other technique is used to "tell" a job to stop gracefully.

      Here's my scenario:
      1. I have a PL/SQL stored procedure (named "MY_PROC") with copious logging and exception handling such that any exception raised during execution is logged and appropriate action taken (e.g. rolling back etc).
      2. I have created a DBMS_SCHEDULER program calling MY_PROC and job which calls the program.
      3. I am able to call my MY_PROC using the job successfully.

      Now, normally, if I call my MY_PROC procedure manually (without DBMS_SCHEDULER) I can interrupt it (from with in SQL*Plus for example) and an exception is raised, captured and logged in MY_PROC.

      However, when I use DBMS_SCHEDULER to run the job and use STOP_JOB, with the force option set to false, the job doesn't stop.
      If I then call STOP_JOB with the force option set to true, the job stops but my MY_PROC procedure knows nothing about being stopped, other than stopping abruptly. That is to say, the procedure ceases to execute but none of the exception handling in MY_PROC is triggered and so I cannot handle the stop gracefully.

      The documentation states:
      "By default, the Scheduler tries to gracefully stop a job using an interrupt mechanism"

      Is there any way to explicitly "tap into" this interrupt mechanism in my PL/SQL procedure to be able to handle being stopped properly?


      Edited by: user652356 on 02-May-2012 18:17
        • 1. Re: Coding a job in PL/SQL to handle being stopped gracefully

          The graceful stop mechanism for dbms_scheduler was designed so that it could not be trapped or intercepted. So it is a bit strange that a graceful stop_job doesn't work on your job (though certainIO opertions are uninterruptible) . But this also makes it difficult to do what you want to do in the job itself.

          Once the job has been stopped however, that is recorded in the SCHEDULER_JOB_LOG and SCHEDULERJOB_RUN_DETAILS views so maybe you could get the information from those views.

          1 person found this helpful
          • 2. Re: Coding a job in PL/SQL to handle being stopped gracefully
            Thanks, Ravi.

            That makes sense. If it wasn't done that way then people could write uninterruptible jobs that could hang systems.

            It's a pity there isn't an "even more graceful" version that can be trapped. Then if that doesn't work the "graceful" can be used, and if that doesn't work then the force version can be used.

            I reckon my procedure was in the middle of a large DELETE operation which is probably why the graceful stop didn't have any effect.

            I think I've decided to just deal with the concequences of calling STOP_JOB manually since (as far as I know) it will only be done by a human. Any other issue should raise an exception and be dealt with in the code.

            Thanks again.