Request For Support To Create DB JOB For ETL Auto Scheduling Twice A Day (Doc ID 2001406.1)

Last updated on JULY 13, 2023

Applies to:

Goal

For one of the customer the auto schedule ETL functionality is not working. It is confirmed as a BUG. Ref: Bug 18364180 - Argus Insight 7.0.3.1 - automatic ETL not working.
So as a temporary work around, we created DB Job to run ETL daily once. However, customer requested to run it twice daily (3AM GMT and 3PM GMT). We need to modify the DB job script to run it twice daily (3AM and 3PM GMT). Would you kindly help us in modifying the DB Job Script.

Here is the script we used to run ETL Daily once:

BEGIN
 SYS.DBMS_JOB.REMOVE(71);
COMMIT;
END;
/

DECLARE
 X NUMBER;
BEGIN
 SYS.DBMS_JOB.SUBMIT
 ( job => X
  ,what => 'begin pkg_dbms_job.p_call_air_incremental(1); end;'
  ,next_date => to_date('08/04/2015 13:30:00','dd/mm/yyyy hh24:mi:ss')
  ,interval => 'TRUNC(SYSDATE+1) + (13.5/24)'
  ,no_parse => FALSE
 );
 SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));

UPDATE CMN_PROFILE_GLOBAL
 SET VALUE = TO_CHAR(x)
 WHERE KEY = '10'
 AND UPPER(NAME) = 'ETL_JOB_ID';
 COMMIT;

COMMIT;
END;
 

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.