Re: DBMS_job submit interval error -- future time

Thanks both guys. I was scheduled with 3 times a days originally. Then the customer wanted to trim to only two times. I forgot to remove the extra 2 binding variables after removal one schedule.

Does anyone figure out my first problem about the difference submit the same job in the morning and afternoon with the future time error?

C Chang
rolympia_at_hotmail.com (Romeo Olympia) wrote in message news:<42fc55dc.0409292257.4ffa87a8_at_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 - 07:36:28 CDT