Re: DBMS_job submit interval error -- future time

From: Romeo Olympia <rolympia_at_hotmail.com>
Date: 29 Sep 2004 23:57:40 -0700
Message-ID: <42fc55dc.0409292257.4ffa87a8@posting.google.com>

As David pointed out, your exception handler is the only thing being run because of an error in your EXECUTE IMMEDIATE. And hence, that error you're getting (only after 1300H).

As an add-on, if there's no particular reason for you to use EXECUTE IMMEDIATE, you could probably just trim that function down to a case call. Something like this:

CREATE OR REPLACE FUNCTION getNextRun(p_date IN DATE)

   RETURN DATE IS

       v_next_run  DATE;
       v_hour      number DEFAULT TO_NUMBER(TO_CHAR(p_date,'hh24'));
       v_now       DATE   DEFAULT TRUNC(p_date);
   BEGIN
       case 
          when v_hour between  0 and 12 then v_next_run :=
v_now+13/24;
          when v_hour between 13 and 17 then v_next_run :=
v_now+18/24;
          else                               v_next_run :=
v_now+1+13/24;
       end case;
        
       RETURN v_next_run;

END getNextRun;
/

Hth.

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
Received on Thu Sep 30 2004 - 01:57:40 CDT