PRC_RFRSH_OCI_COST_DATA.sql

CREATE OR REPLACE PROCEDURE PRC_RFRSH_OCI_COST_DATA IS
   l_ctr NUMBER;
BEGIN
    -- check any runnig jobs
    SELECT COUNT(LOG_ID)
      INTO l_ctr
      FROM DBA_SCHEDULER_RUNNING_JOBS
     WHERE  1=1
       AND job_name LIKE 'PRC_RFRSH_OCI_COST_DATA_JOB%'
       AND SESSION_ID != SYS_CONTEXT('USERENV','SID');

    IF l_ctr > 0 THEN
        DBMS_OUTPUT.put_line('there is already a running job');
        RETURN;
    END IF;

    --refresh temp table
    DBMS_MVIEW.refresh('OCI_COST_DATA_MV_TMP','C',atomic_refresh=>FALSE);
    DBMS_OUTPUT.put_line('OCI_COST_DATA_MV_TMP refreshed');

    --merge insert new rows
    INSERT INTO OCI_COST_DATA_MV
    SELECT t.*
     FROM OCI_COST_DATA_MV_TMP t, OCI_COST_DATA_MV d
    WHERE 1=1
      AND t.REFERENCE_NUMBER = d.REFERENCE_NUMBER(+)
      AND d.REFERENCE_NUMBER IS NULL;
   DBMS_OUTPUT.put_line(SQL%ROWCOUNT||' rows inserted');

    IF 1=1 THEN
    -- submit next job for the next hour
    DBMS_SCHEDULER.create_job (
            job_name        => 'PRC_RFRSH_OCI_COST_DATA_JOB_'||SYS_GUID(),
            job_type        => 'PLSQL_BLOCK',
            job_action      => 'BEGIN PRC_RFRSH_OCI_COST_DATA; END;',
            start_date      => SYSTIMESTAMP + INTERVAL '6' HOUR,
            repeat_interval => NULL,--'freq=hourly; byminute=0; bysecond=0;',
            end_date        => NULL,
            enabled         => TRUE,
            comments        => 'Auto-refresh OCI_COST_DATA_MV'
    );
    END IF;
END PRC_RFRSH_OCI_COST_DATA;