DBMS_job submit interval error -- future time

From: C Chang <chi-soon_x_chang_at_raytheon.com>
Date: 29 Sep 2004 12:01:12 -0700
Message-ID: <88c62e86.0409291101.494d6d9d@posting.google.com>

I have a function return time for specific hour a day: (this is adopted from askTom site)
CREATE OR REPLACE FUNCTION getNextRun(p_date IN DATE)

   RETURN DATE IS

       v_next_run  DATE;
       v_hour      number DEFAULT TO_CHAR(p_date,'hh24');
       v_now       DATE   DEFAULT TRUNC(p_date);
   BEGIN
       EXECUTE IMMEDIATE '
       SELECT case when :v_hour between  0 and 12 then :v_now+13/24
                   when :v_hour between 13 and 17 then :v_now+18/24
                   else                                :v_now+1+13/24
              end
       FROM dual'
       INTO v_next_run
       USING v_hour, v_now, v_hour, v_now, v_hour, v_now, v_now;
        
       RETURN v_next_run;
   EXCEPTION
       WHEN OTHERS THEN
          RETURN  TRUNC(sysdate)+13/24;

END getNextRun;
/

Then I submit a dbms job as
begin
  dbms_job.submit(:n,

                  'null;',
                  trunc(sysdate)+18/24,
                  'getNextRun(sysdate)' );

end;
/

the submit will generate error of
ERROR at line 1:

ORA-23420: interval must evaluate to a time in the future
ORA-06512: at "SYS.DBMS_JOB", line 57
ORA-06512: at "SYS.DBMS_JOB", line 129
ORA-06512: at line 2

in the afternoon but NOT IN THE MORNING. Even I change the sysdate+1 in the getNextRun function but still causes error. Try to understand why? Can anyone help?
The second problem I have is that when I submit it with next_date section as trunc(sysdate)+13/24, this morning, after 13:00 hour the next_sec became 02:30:34, cann ot figure why from my getNextRun. Hope someone can pinpoint the cause? Thanks.

C Chang Received on Wed Sep 29 2004 - 14:01:12 CDT