title: Adding exception handling and improving the below stored procedure and job in oracle
body: <p>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. </p>
  
  <pre><code>BEGIN
    DBMS_SCHEDULER.create_job (
      job_name        =&gt; 'TABLE_CLEAN_UP_JOB',
      job_type        =&gt; 'STORED_PROCEDURE',
      job_action      =&gt; 'TABLE_CLEAN_UP',
      start_date      =&gt; SYSTIMESTAMP,
      repeat_interval =&gt; 'freq=hourly;interval=12',
      enabled         =&gt; TRUE);
  END;
  /
  </code></pre>
  
  <p>below is the stored  procedure</p>
  
  <pre><code>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) &gt; refresh_date) then
                    i := i + 1;  
        end if; 
       END LOOP;
  
       if i=2 then
              delete DC_CASHFLOW_DELTA;
       end if; 
     end if;
  
  end;
  /
  </code></pre>
  
  <p>now there are two concerns with respect to this</p>
  
  <p>first I haven't done the proper exception handling so please advise 
  how to proper exception handling in above stored procedure</p>
  
  <p>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.</p>
  
  <p>Please advise folks how the improvements could be done
  any suggestions would be appreciated </p>
  
  <p>Folks can somebody pls advise on this</p>