6 Replies Latest reply: Apr 9, 2014 1:23 AM by anwickes RSS

    Schedule Trigger

    Kaveri2510

      Hi All,

       

      I have a requirement where I have to schedule a job which runs every day between 6:00am to 5:00 pm

       

      This is the Query I have given in the Schedule Trigger of the Report's data model :

       

      select 'true' from dual

      where (SELECT  EXTRACT(HOUR FROM localtimestamp) || ':' ||EXTRACT(MINUTE FROM localtimestamp)|| ':' ||EXTRACT(SECOND FROM localtimestamp) FROM dual)

      between '06:00:00' and '17:00:00'

       

       

      But my job fails giving the following error :

       

      Detail Message:

      ::FAILED_TO_ACCESS_TRIGGER_DATA_MODEL::[INSTANCE_ID=***] [JOB_ID=3051]::

      null::

      1. java.lang.NullPointerException

                      at oracle.xdo.servlet.ReportRepository.getDataModelFromCache(ReportRepository.java:503)

                      at oracle.xdo.enterpriseScheduler.SchedulerJob.execute(SchedulerJob.java:110)

                      at oracle.xdo.service.scheduling.impl.quartz.QuartzJob.execute(QuartzJob.java:24)

                      at org.quartz.core.JobRunShell.run(JobRunShell.java:202)

                      at org.quartz.simpl.SimpleThreadPool$Work

       

      Kindly let me know how to go about it

      Thanks

        • 1. Re: Schedule Trigger
          Goran_1976

          I suppose that this query is part of a function and that you handle NO_DATA_FOUND when the WHERE part is false inside that function.


          My function would be:

          CREATE OR REPLACE FUNCTION bi_report_window(i_date IN DATE DEFAULT NULL)  RETURN BOOLEAN IS
          BEGIN
            RETURN NVL(i_date, LOCALTIMESTAMP) BETWEEN TRUNC( LOCALTIMESTAMP) + 6 / 24 AND TRUNC( LOCALTIMESTAMP) + 17 / 24;
          END bi_report_window;
          

           

          Test:

          DECLARE
            v_date DATE;
          
            PROCEDURE test IS
            BEGIN
              IF bi_report_window(v_date) THEN
                dbms_output.put_line(TO_CHAR(v_date, 'HH24:MI:SS') || ' TRUE');
              ELSE
                dbms_output.put_line(TO_CHAR(v_date, 'HH24:MI:SS') || ' FALSE');
              END IF;
            END;
          BEGIN
          
          
            FOR i IN 0 .. 23 LOOP
              IF i IN (6, 17) THEN
                v_date := TRUNC(LOCALTIMESTAMP) + i / 24 - 1/86400;
                test;
              END IF;
             
              v_date := TRUNC(LOCALTIMESTAMP) + i / 24;
              test;   
             
              IF i IN (6, 17) THEN
                v_date := TRUNC(LOCALTIMESTAMP) + i / 24 + 1/86400;
                test;
              END IF;  
            END LOOP;
          END;
          
          • 2. Re: Schedule Trigger
            Kaveri2510

            When a report job is scheduled to run, the schedule trigger executes the SQL statement defined for the trigger. If data is returned, then the report job is submitted. If data is not returned from the trigger SQL query, the report job is skipped.

             

            So If it is out of the time period specified in the query, null is returned and Job should be skipped.

             

            But my issue is the job is failing at the given time period and I get the "FAILED_TO_ACCESS_TRIGGER_DATA_MODEL" error

            • 3. Re: Schedule Trigger
              anwickes

              Hi Kaveri,

               

              Were you able to fix your issues with this?

              We are struggling with the same.

               

              It also seems like every time we perform a restart of the services, it causes multiple reports to fail the morning after.

              Otherwise, it is quite intermittent.

               

               

              Cheers,

              Adam

              • 4. Re: Schedule Trigger
                anwickes

                Have to bump.... Is anyone else having these issues? Oracle are absolutely no help whatsoever!!!

                • 5. Re: Schedule Trigger
                  AlexAnd

                  did you see

                   

                  Exception "FAILED_TO_ACCESS_TRIGGER_DATA_MODE" When Running Scheduled Job in BI Publisher 11G (Doc ID 1633734.1)


                  BI Publisher Job Errors intermittently With Error FAILED_TO_ACCESS_TRIGGER_DATA_MODEL (Doc ID 1619312.1)

                   

                  ?

                  • 6. Re: Schedule Trigger
                    anwickes

                    Applied and it still does not work.

                     

                     

                    EDIT: I lied.... this is a different patch. I'll let you how it goes. Thankyou very much!!!