--
-- Refresh materialized view 
-- after prompting a user for the view name.
--
-- Created:
--          8-Jan-02 Stan Krzywdzinski
--
--
SET FEEDBACK OFF
SET HEADING OFF
SELECT 'DATE:  ' || TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS') FROM DUAL;
PROMPT You may refresh the following materialized views:
SELECT mview_name from user_mviews;
PROMPT
ACCEPT mview CHAR PROMPT 'Enter materialized view name to refresh > '
SET VERIFY OFF
SELECT 'The mview was last refreshed on:       ' || 
  TO_CHAR(last_refresh, 'DD-MON-YY HH24:MI:SS') FROM 
  user_snapshot_refresh_times WHERE owner in
  (select username from user_users) AND
  name = UPPER('&mview');
SELECT 'It will be refreshed automatically on: ' ||
  TO_CHAR(next_date, 'DD-MON-YY HH24:MI:SS') FROM
  user_jobs WHERE what like UPPER('%&mview%'); 
PROMPT
ACCEPT refresh_confirm PROMPT 'Refresh &mview now (Y/N) ? '
PROMPT
--
SET SERVEROUTPUT ON
DECLARE
  user_yn_response CHAR := UPPER('&&refresh_confirm');
BEGIN
  IF    user_yn_response = 'Y' THEN
    DBMS_OUTPUT.PUT_LINE('Refreshing materialized view &mview now ...');
    DBMS_REFRESH.REFRESH('&mview');
    DBMS_OUTPUT.PUT_LINE('... done');
  ELSIF user_yn_response = 'N' THEN
    DBMS_OUTPUT.PUT_LINE('No refreshing of materialized view &mview was done.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('You must answer with a Y or N.');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('The materialized view &mview could not be refreshed. '
                         || SQLERRM);
END;
/

SET VERIFY ON
--PROMPT Refreshing materialized view '&mview' now ... 
--
--EXECUTE DBMS_REFRESH.REFRESH('&mview');
--
--PROMPT ... done 
--
SELECT 'DATE:  ' || TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS') FROM DUAL;
--
SET HEADING ON
SET FEEDBACK ON
