This discussion is archived
7 Replies Latest reply: Nov 8, 2012 11:37 AM by EdStevens RSS

Testing DST scenarios, specifying repeat_interval

EdStevens Guru
Currently Being Moderated
Oracle 11.2.0.1 SE-ONE
Oracle Linux 5.6 x86-64

Just went through my first DST change at this shop and there have been a lot of issues. Seems most jobs were specified with start_dates using time offsets instead of timezone region names, so DST change was not observed, causing first run of a job after the change to be one hour early. I think I understand the fix and am setting up a variety of test scenarios to verify.

As an additional twist, most of the jobs are defined with this type of syntax for the repeat_interval:
MON_TO_FRI(.4375)
Several variations on that theme. Not hard to look at it and figure out what it means (multiplying the decimal by 24 to get a time of day), but I've not been able to actually create a job like that. What I get is
SQL> begin
  2    dbms_scheduler.create_job('JOB_MYTEST_01',
  3                       'PLSQL_BLOCK',
  4                       'begin null; end;',
  5                       enabled => true,
  6                       start_date => '20-MAR-10 10.59.57 AM -05:00',
  7                       repeat_interval => 'daily(.58333)'
  8                       );
  9  end;
 10  /
begin
*
ERROR at line 1:
ORA-27465: invalid value daily(.58333) for attribute REPEAT_INTERVAL
ORA-06512: at "SYS.DBMS_ISCHED", line 124
ORA-06512: at "SYS.DBMS_SCHEDULER", line 271
ORA-06512: at line 2
I'm guessing that in the existing jobs in my production system, the repeat_interval resulted from a pl/sql expression, but I'm at a loss on how to reproduce it.
  • 1. Re: Testing DST scenarios, specifying repeat_interval
    spajdy Pro
    Currently Being Moderated
    Scheduler REPEAT_INTERVAL could be defined in two syntax:
    calendar - more flexible and readable for human
    pl/sql - as old dbms_job is an expression that is evaluated to date

    I suppose that mon_to_fri is PL/SQL function on your system. You should simply check it by
    select * from dba_objects where object_name='MON_TO_FRI';
    If you want to use it simply put this expression to repeat_interval parameter in dbms_scheduler.create_job procedure.
  • 2. Re: Testing DST scenarios, specifying repeat_interval
    EdStevens Guru
    Currently Being Moderated
    spajdy wrote:
    Scheduler REPEAT_INTERVAL could be defined in two syntax:
    calendar - more flexible and readable for human
    pl/sql - as old dbms_job is an expression that is evaluated to date
    Yes, I know that part.
    I suppose that mon_to_fri is PL/SQL function on your system. You should simply check it by
    select * from dba_objects where object_name='MON_TO_FRI';
    Yes, I know that part.
    If you want to use it simply put this expression to repeat_interval parameter in dbms_scheduler.create_job procedure.
    Yes, but if you'll check what I posted, you'll see that is exactly what I tried to do. Obviously I have some issue with exactly how to specify it, and the docs aren't providing a lot of help in that area.
  • 3. Re: Testing DST scenarios, specifying repeat_interval
    RnR Pro
    Currently Being Moderated
    Hi,

    Just to check could you post the results of

    select daily(.58333) from dual ;

    as that user so we can see what that looks like ? That should return a single date.

    Thanks,
    Ravi.
  • 4. Re: Testing DST scenarios, specifying repeat_interval
    EdStevens Guru
    Currently Being Moderated
    RnR wrote:
    Hi,

    Just to check could you post the results of

    select daily(.58333) from dual ;

    as that user so we can see what that looks like ? That should return a single date.

    Thanks,
    Ravi.
    of course it does
    SQL> select daily(.58333) from dual;
    
    DAILY(.58
    ---------
    08-NOV-12
    
    SQL> select to_char(daily(.58333),'dd-mon-yyyy hh24:mi:ss') from dual;
    
    TO_CHAR(DAILY(.58333
    --------------------
    08-nov-2012 14:00:00
    
    SQL>
  • 5. Re: Testing DST scenarios, specifying repeat_interval
    spajdy Pro
    Currently Being Moderated
    I suppose that you run
    select daily(.58333) from dual ;
    and
    begin
     dbms_scheduler.create_job(...
    end;
    under same DB user with same NLS settings.
    Am I right ?

    Becasue I try it on my DB and it works:
    SELECT * FROM v$version;
    CREATE OR REPLACE FUNCTION daily(p IN NUMBER) RETURN DATE AS
    BEGIN
      RETURN SYSDATE+p;
    END;
    /
    
    select daily(.58333) from dual ;
    
    begin
        dbms_scheduler.create_job('JOB_MYTEST_01',
                       'PLSQL_BLOCK',
                       'begin null; end;',
                       enabled => true,
                           start_date => SYSDATE,
                           repeat_interval => 'daily(.58333)'
                           );
    end;
    /  
    SELECT * FROM user_scheduler_jobs WHERE job_name='JOB_MYTEST_01';
    and result is:
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
    PL/SQL Release 11.1.0.7.0 - Production
    CORE     11.1.0.7.0     Production
    TNS for HPUX: Version 11.1.0.7.0 - Production
    NLSRTL Version 11.1.0.7.0 - Production
     
    Function created
     
    DAILY(.58333)
    -------------
    09.11.2012 5:
     
    PL/SQL procedure successfully completed
     
    JOB_NAME                       JOB_SUBNAME                    JOB_STYLE   JOB_CREATOR                    CLIENT_ID                                                        GLOBAL_UID                       PROGRAM_OWNER                                                                    PROGRAM_NAME                                                                     JOB_TYPE         JOB_ACTION                                                                       NUMBER_OF_ARGUMENTS SCHEDULE_OWNER                                                                   SCHEDULE_NAME                                                                    SCHEDULE_TYPE START_DATE                                        REPEAT_INTERVAL                                                                  EVENT_QUEUE_OWNER              EVENT_QUEUE_NAME               EVENT_QUEUE_AGENT                                                                EVENT_CONDITION                                                                  EVENT_RULE                                                        END_DATE                                          JOB_CLASS                      ENABLED AUTO_DROP RESTARTABLE STATE           JOB_PRIORITY  RUN_COUNT   MAX_RUNS FAILURE_COUNT MAX_FAILURES RETRY_COUNT LAST_START_DATE                                   LAST_RUN_DURATION   NEXT_RUN_DATE                                     SCHEDULE_LIMIT      MAX_RUN_DURATION    LOGGING_LEVEL STOP_ON_WINDOW_CLOSE INSTANCE_STICKINESS RAISE_EVENTS                                                                     SYSTEM JOB_WEIGHT NLS_ENV                                                                          SOURCE                                                                           DESTINATION                                                                      CREDENTIAL_OWNER               CREDENTIAL_NAME                INSTANCE_ID DEFERRED_DROP COMMENTS                                                                              FLAGS
    ------------------------------ ------------------------------ ----------- ------------------------------ ---------------------------------------------------------------- -------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------- -------------------------------------------------------------------------------- ------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------- ------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------------------------------------------------------- ------------------------------------------------- ------------------------------ ------- --------- ----------- --------------- ------------ ---------- ---------- ------------- ------------ ----------- ------------------------------------------------- ------------------- ------------------------------------------------- ------------------- ------------------- ------------- -------------------- ------------------- -------------------------------------------------------------------------------- ------ ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ----------- ------------- -------------------------------------------------------------------------------- ----------
    JOB_MYTEST_01                                                 REGULAR     DBADMIN                                                                                                                                                                                                                                                                                            PLSQL_BLOCK      begin null; end;                                                                                   0                                                                                                                                                                   PLSQL         08.11.12 15:57:07,000000 +01:00                   daily(.58333)                                                                                                                                                                                                                                                                                                                                                                                                                        DEFAULT_JOB_CLASS              TRUE    TRUE      FALSE       SCHEDULED                  3          1                        0                        0 08.11.12 15:57:07,198065 +01:00                   +000000000 00:00:00 09.11.12 05:57:07,000000 +01:00                                                           OFF           FALSE                TRUE                                                                                                 FALSE           1 NLS_LANGUAGE='ENGLISH' NLS_TERRITORY='CZECH REPUBLIC' NLS_CURRENCY='Kč' NLS_ISO_                                                                                                                                                                                                                                             FALSE                                                                                              131632
     
    Could you give us your daily function ?
  • 6. Re: Testing DST scenarios, specifying repeat_interval
    EdStevens Guru
    Currently Being Moderated
    spajdy wrote:
    I suppose that you run
    select daily(.58333) from dual ;
    and
    begin
    dbms_scheduler.create_job(...
    end;
    under same DB user with same NLS settings.
    Am I right ?
    yes, of course.
    Becasue I try it on my DB and it works:
    SELECT * FROM v$version;
    CREATE OR REPLACE FUNCTION daily(p IN NUMBER) RETURN DATE AS
    BEGIN
    RETURN SYSDATE+p;
    END;
    /
    Bingo!


    I assumed that "daily" was a standard scheduler function that I simply wasn't familiar with. Turns out it is home-grown, owned by the application schema, and so doesn't exist on my sandbox system that I use for a lot of my low-level testing. And now that I see that, I see that we have a whole boat-load of them. Apparently my predecessor was too clever by half in setting up job scheduling.

    <snip>
  • 7. Re: Testing DST scenarios, specifying repeat_interval
    EdStevens Guru
    Currently Being Moderated
    >
    <snip>
    >
    Could you give us your daily function ?
    And now that I see the issue, this statement makes more sense. I thought you were asking what I do every day - my "daily function". Now I see you were asking about a 'function' named DAILY.

    "Could you give us the code for your function, 'DAILY' ?"

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points