5 Replies Latest reply: Apr 2, 2013 5:31 AM by BEDE RSS

    run procedure only from 9:00 PM till 3:00 AM

    marco
      Hi all,

      how do I run procedure only from 9:00 PM till 3:00 AM ?
      I mean, procedure should not start if system time is other than "from 9:00 PM till 3:00 AM" even if someone has started it.
        • 1. Re: run procedure only from 9:00 PM till 3:00 AM
          bencol
          Put this as the first line in your procedure:
          if to_number(to_char(sysdate,'hh24')) between 3 AND 20 then
            raise <user defined exception, application_error etc.>;
          end if;
          <rest of your code>
          • 2. Re: run procedure only from 9:00 PM till 3:00 AM
            jeneesh
            marco wrote:
            Hi all,

            how do I run procedure only from 9:00 PM till 3:00 AM ?
            I mean, procedure should not start if system time is other than "from 9:00 PM till 3:00 AM" even if someone has started it.
            Just after begin add the below code
            if sysdate >= trunc(sysdate)+(21/24) or sysdate <= trunc(sysdate)+(3/24) then
              raise_application_error(-20001,'Error- You cannot run this procedure now');
            end if;
            • 3. Re: run procedure only from 9:00 PM till 3:00 AM
              Frank Kulash
              Hi,

              I don't think there's any way to keep a procedure from running at given times.
              You can keep a procedeure from doing anything at given times:
              ...
              BEGIN
                  IF  TO_CHAR (SYSDATE, 'HH24:MI')  BETWEEN  '03:00'
                                              AND      '21:00'
                  THEN
                      ... 
                  END IF;
              END proc_x;
              Poeple can call this proceeure at any time of the day, but if they do so between 9:00 PM and 3:00 AM, will return immediately.
              • 4. Re: run procedure only from 9:00 PM till 3:00 AM
                908002
                add a condition before start of statements in procedure for current time falls between 9:00 PM till 3:00 AM
                • 5. Re: run procedure only from 9:00 PM till 3:00 AM
                  BEDE
                  Well, you have the sysdate, that you will use in the following way:
                    if sysdate between trunc(sysdate) and trunc(sysdate)+3/24 
                      or sysdate between trunc(sysdate)+9/24 and trunc(sysdate)+1 then
                       --- do whatever processing you need
                          null;
                    else
                      -- not business hours
                      raise_application_error(-20113,'Not allowed to run X unless between 0 and 3 or 21 and 24 hours!');
                    end if;