Re: DBMS_job submit interval error -- future time

"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 - 08:54:30 CDT