6 Replies Latest reply: Apr 8, 2014 11:23 PM by anwickes RSS

Schedule Trigger

Kaveri2510 Newbie
Currently Being Moderated

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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

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

  • 5. Re: Schedule Trigger
    AlexAnd Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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!!!

Legend

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