Re: DBMS_job submit interval error -- future time

David:
Great thanks. By pointing out the Exception from the function, Now I understand what actually the system was doing in the back when I submited a dbms_job which was involved a function call. (even the job is really not RUN immediatelly). tested. No error in the afternoon now.

C Chang

fitzjarrell_at_cox.net (David Fitzjarrell) wrote in message news:<9711ade0.0409300554.59e4de69_at_posting.google.com>...
> "C Chang" <chi-soon_x_chang_at_raytheon.com> wrote in message news:88c62e86.0409300436.6632921c_at_posting.google.com...
> > 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
>
> The answer to THAT question is right in front of your face in my prior
> post noting that the ONLY section of your function actually doing any
> work was the EXCEPTION handler. Look at your original code carefully,
> and note what the EXECPTION handler is doing:
>
> EXCEPTION
> WHEN OTHERS THEN
> RETURN TRUNC(sysdate)+13/24;
>
> If you cannot see the obvious (which, apparently, you cannot) you are
> returning nothing more than the CURRENT DATE at 13:00. Logic dictates
> that at some point during a given day 13:00 will be in the PAST. This
> is, of course, WHY you ran into your dbms_job scheduling problem to
> begin with. You encountered this because you decided to name your
> EXECUTE IMMEDIATE bind variables to match your declared PL/SQL
> variables; hence, they could NOT be found and the entire SELECT
> statement was ignored. Far MORE was changed in my code than you are
> willing to see: the bind variable names, the number of parameters in
> the EXECUTE IMMEDIATE call, the implicit number conversion in the
> v_hour variable declaration. Such changes corrected YOUR code to make
> it work as desired. Romeo Olympia also provided a much cleaner
> solution; I didn't take the time to re-write the function, I simply
> made corrections to your code to make it work as desired.
>
> Your questions were answered in my original post; you, unfortunately,
> could not see the forest for the trees.
>
> David Fitzjarrell
>
>
> > 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 - 17:36:11 CDT