Re: DBMS_job submit interval error -- future time

From: David Fitzjarrell <fitzjarrell_at_cox.net>
Date: 29 Sep 2004 15:17:38 -0700
Message-ID: <9711ade0.0409291417.4f667457@posting.google.com>

chi-soon_x_chang_at_raytheon.com (C Chang) wrote in message news:<88c62e86.0409291101.494d6d9d_at_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

Your execption section was hiding the source of your problems; removing it for a run revealed:

SQL> select sysdate, getNextRun(sysdate) from dual; select sysdate, getNextRun(sysdate) from dual

                *

ERROR at line 1:
ORA-01006: bind variable does not exist
ORA-06512: at "BLONK.GETNEXTRUN", line 7

Here is the correct function:

CREATE OR REPLACE FUNCTION getNextRun(p_date IN DATE)

   RETURN DATE IS

       v_next_run  DATE;
       v_hour      number := TO_NUMBER(TO_CHAR(p_date,'HH24'));
       v_now       DATE   := TRUNC(p_date);
   BEGIN
       EXECUTE IMMEDIATE '
       SELECT case when :vhour between  0 and 12 then :vnow+13/24
                   when :vhour between 13 and 17 then :vnow+18/24
                   else                                :vnow+1+(13/24)
              end
       FROM dual'
       INTO v_next_run
       USING 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;
/

This returns:

SQL> select sysdate, getNextRun(sysdate) from dual;

SYSDATE GETNEXTRUN(SYSDATE)

-------------------- --------------------

29-SEP-2004 22:15:42 30-SEP-2004 13:00:00 Which is precisely what you want, if I read this correctly.

David Fitzjarrell Received on Wed Sep 29 2004 - 17:17:38 CDT