2 Replies Latest reply: Nov 12, 2012 3:02 AM by Marwim RSS

    Continue the execution when errors

    864988
      Hello,

      My database is 11gR2; I have hundreds of materialized views to be refreshed in one sql script (Linux).
      I want to continue mviews refreshing whenever there is any ORA error captured.
      I have seen there is; whenever sqlerror continue command;
      could someone tell me best way to develop this in sql script?
      BEGIN
        DBMS_mVIEW.REFRESH('MTREESTRCT','C');
        DBMS_mVIEW.REFRESH('MXLATITEM','C');
        DBMS_mVIEW.REFRESH('MXLATITEMLANG','C');
        DBMS_mVIEW.REFRESH('MTREENODE','C');
        DBMS_mVIEW.REFRESH('MSTATUS','C');
        DBMS_mVIEW.REFRESH('MTREEDEFN','C');
        DBMS_mVIEW.REFRESH('MRECDEFNLANG','C');
        DBMS_mVIEW.REFRESH('MRECFIELD','C');
        DBMS_mVIEW.REFRESH('MRECDEFN','C');
        DBMS_mVIEW.REFRESH('MPRCSRQST','C');
        DBMS_mVIEW.REFRESH('MPNLGROUPLANG','C');
        DBMS_mVIEW.REFRESH('MPNLGROUP','C');
        DBMS_mVIEW.REFRESH('MPNLDEFN','C');
        DBMS_mVIEW.REFRESH('MOPTIONS','C');
        DBMS_mVIEW.REFRESH('MOPRDEFN','C');
        DBMS_mVIEW.REFRESH('MMENUDEFNLANG','C');
        DBMS_mVIEW.REFRESH('MMENUDEFN','C');
        DBMS_mVIEW.REFRESH('MDBFIELDLANG','C');
        DBMS_mVIEW.REFRESH('MDBFIELD','C');
        DBMS_mVIEW.REFRESH('M_ADDRESS_TYP_TBL','C');
      END;
        • 1. Re: Continue the execution when errors
          jeneesh
          Like
          BEGIN
            begin
              DBMS_mVIEW.REFRESH('MTREESTRCT','C');
            exception
              when others then 
                --log your errors
            end;
            --2nd mview
            begin
              DBMS_mVIEW.REFRESH('MXLATITEM','C');
            exception
              when others then 
                --log your errors
            end;
            ....
          END;
          • 2. Re: Continue the execution when errors
            Marwim
            Or write a procedure that does what jeenesh has suggested and call it for each MV
            CREATE OR REPLACE PROCEDURE my_refresh_mv(
             p_mv_name IN VARCHAR2
             )
            IS
            BEGIN
                dbms_mview.refresh(p_mv_name,'C');
            EXCEPTION
                WHEN OTHERS THEN
                  --log your errors
            END my_refresh_mv;
            /
            BEGIN
                my_refresh_mv('MTREESTRCT');
                my_refresh_mv('MXLATITEM');
                ...
            END;
            /
            Regards
            Marcus