-- Last and next times of refresh jobs.
--
-- Created:
--          7-Jan-02 Stan Krzywdzinski
--
--
SET HEADING OFF
SELECT 'Date:  ' || TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS') FROM DUAL;
SELECT 'Refresh jobs for user ' || username || ':' FROM user_users;
SET HEADING ON
SET SERVEROUTPUT ON
--
DECLARE
  jobs NUMBER := 0;
  refresh NUMBER;
  CURSOR job_cursor IS
    SELECT job, what, TO_CHAR(last_date,'DD-MON-YY HH24:MI:SS') last,
                      TO_CHAR(next_date,'DD-MON-YY HH24:MI:SS') next
		      FROM user_jobs order by job;
BEGIN
--  DBMS_OUTPUT.ENABLE;
  DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------------');
  FOR job_data IN job_cursor LOOP
    refresh := INSTR(LOWER(job_data.what),'dbms_refresh.refresh');
    IF refresh > 0 THEN
      jobs := jobs + 1;
      DBMS_OUTPUT.PUT_LINE('Job #: ' || TO_CHAR(job_data.job,'999999') || '  ' || job_data.what);
      DBMS_OUTPUT.PUT_LINE('Last date: ' || job_data.last);
      DBMS_OUTPUT.PUT_LINE('Next date: ' || job_data.next);
      DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------------');
    END IF;
  END LOOP;
  IF jobs = 0 THEN
    DBMS_OUTPUT.PUT_LINE('None found.');
  END IF;
END;
/
--
SET SERVEROUTPUT OFF
