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;
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;
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
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.
Have to bump.... Is anyone else having these issues? Oracle are absolutely no help whatsoever!!!
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!!!