Adding exception handling and improving the below stored procedure and job in oracle

Question

I have written database scheduler job in oracle database which is currently configured to run after every 12 hours. This job call a stored procedure which does the delta table clean up task only if mv refresh is complete and mv last refresh time is greater than last run time of clean up job.

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'TABLE_CLEAN_UP_JOB',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'TABLE_CLEAN_UP',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly;interval=12',
    enabled         => TRUE);
END;
/

below is the stored procedure

create or replace procedure TABLE_CLEAN_UP
is
refresh_date timestamp(6);
v_exists NUMBER;
outcome VARCHAR2(100);
cnt NUMBER;
i NUMBER := 0;
begin

SELECT count(1) into cnt FROM all_mviews WHERE owner = 'M_TO' AND mview_name = 'DC_CASHFLOW_VIEW' or  mview_name = 'DC_CASHFLOW_VIEW_ZERO' and LAST_REFRESH_TYPE='COMPLETE';

   if cnt=2 then
     FOR rec IN (SELECT * FROM all_mviews WHERE owner = 'P_SM_TO' AND mview_name = 'DC_CASHFLOW_VIEW' or  mview_name = 'DC_CASHFLOW_VIEW_ZERO' and LAST_REFRESH_TYPE='COMPLETE')
     LOOP
            Select LAST_START_DATE into refresh_date from USER_SCHEDULER_JOBS 

where JOB_NAME='TABLE_CLEAN_UP_JOB';
          if (CAST(rec.LAST_REFRESH_DATE AS TIMESTAMP) > refresh_date) then
                  i := i + 1;  
      end if; 
     END LOOP;

     if i=2 then
            delete DC_CASHFLOW_DELTA;
     end if; 
   end if;

end;
/

now there are two concerns with respect to this

first I haven't done the proper exception handling so please advise how to proper exception handling in above stored procedure

also please advise how can incorporate the logic in a loop.. if the clean-up doesn’t happen, it should sleep for 15 mins and then re-attempt.

Please advise folks how the improvements could be done any suggestions would be appreciated

Folks can somebody pls advise on this

Answer 1

Here is a cleaned-up version (untested of course). I took out the loop as it seemed to boil down to two counts, and I changed the or constructions in your where clauses to in () as it's simpler and you were missing some brackets so it would have given incorrect results.

I'm never keen on hardcoding schema names - perhaps they should be passed as parameters or fetched from a config table?

The exception handling looks fine as it is. (It will fail with a no_data_found if there is no 'TABLE_CLEAN_UP_JOB', but I'd say it should fail in that situation because part of the system is missing. Maybe an exception handler for that specific case could provide a better message using raise_application_error, or just log a message and continue, if that's what you want it to do. It's up to you.)

create or replace procedure table_clean_up
is
    v_refresh_date   date;
    v_table_count_m  integer;
    v_table_count_p  integer;

begin
    select count(*) into v_table_count_m
    from   all_mviews
    where  owner = 'M_TO'
    and    mview_name in ('DC_CASHFLOW_VIEW','DC_CASHFLOW_VIEW_ZERO')
    and    last_refresh_type = 'COMPLETE';

    if v_table_count_m = 2 then
        select cast(last_start_date as date) into v_refresh_date
        from   user_scheduler_jobs
        where  job_name = 'TABLE_CLEAN_UP_JOB';

        select count(*) into v_table_count_p
        from   all_mviews m
        where  m.owner = 'P_SM_TO'
        and    m.mview_name in ('DC_CASHFLOW_VIEW', 'DC_CASHFLOW_VIEW_ZERO')
        and    m.last_refresh_type = 'COMPLETE'
        and    m.last_refresh_date > v_refresh_date;

        if v_table_count_p = 2 then
            delete dc_cashflow_delta;
        end if;
    end if;

end;

I didn't quite understand the scheduling question. You have a job 'TABLE_CLEAN_UP_JOB' that calls the above procedure every 12 hours, and inside the procedure you check for some MV refreshes since the last job run (12 hours ago), but if there weren't any, then you want to reschedule it to retry in 15 minutes. I am probably missing something, but why not just schedule it to run every 15 minutes in the first place?

Anyway if you really wanted one procedure call to sit there retrying for up to 12 hours, you might try something along the lines of the following:

create or replace procedure table_clean_up
is
    v_refresh_date   date;
    v_table_count_m  integer;
    v_table_count_p  integer;
    v_loopcount      integer := 48;
begin
    while v_loopcount > 0 loop
        select count(*) into v_table_count_m
        from   all_mviews
        where  owner = 'M_TO'
        and    mview_name in ('DC_CASHFLOW_VIEW','DC_CASHFLOW_VIEW_ZERO')
        and    last_refresh_type = 'COMPLETE';

        if v_table_count_m = 2 then
            select cast(last_start_date as date) into v_refresh_date
            from   user_scheduler_jobs
            where  job_name = 'TABLE_CLEAN_UP_JOB';

            select count(*) into v_table_count_p
            from   all_mviews m
            where  m.owner = 'P_SM_TO'
            and    m.mview_name in ('DC_CASHFLOW_VIEW', 'DC_CASHFLOW_VIEW_ZERO')
            and    m.last_refresh_type = 'COMPLETE'
            and    m.last_refresh_date > v_refresh_date;

            if v_table_count_p = 2 then
                delete dc_cashflow_delta;
                exit;
            end if;
        end if;

        dbms_lock.sleep(60 * 15);
        v_loopcount := v_loopcount -1;
    end loop;
end;

(The magic numbers for how long to sleep and how many iterations to attempt should probably be passed in as parameters or configured in a table.)