7 Replies Latest reply: Nov 8, 2012 1:37 PM by EdStevens RSS

    Testing DST scenarios, specifying repeat_interval

    EdStevens
      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
          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
            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-Oracle
              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
                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
                  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
                    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
                      >
                      <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' ?"