title: Oracle : Materialized View vs Stored Procedure in terms of huge data at referesh time
body: <p>My Materialized view underlying table is having 6M of records and its going for COMPLETE refresh each time. The MVIEW underlying query is having 18 table join(all table in the same schema). Its blocking the PGA memory each time when refresh happening and I am getting warning. So my question is below.</p>
  
  <p>1) Shall I change the Materialized view to a stored procedure? Stored procedure will have the cursor with having same query as Mview and it will load the data to a new table by using BULK COLLECT and LIMIT 1000 rows at a time. We will write an Autosys job(daily once at non business hour) to call the procedure.</p>
  
  <p>Below is my changed PROC:</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE proc_reporting IS
  
  CURSOR cur_rows IS
  (Old MVIEW query)
  
  TYPE reporting_type IS TABLE OF reporting_test%ROWTYPE INDEX BY PLS_INTEGER;
  l_reporting_type reporting_type;
  
  BEGIN
  
  EXECUTE IMMEDIATE 'TRUNCATE TABLE reporting_test';
  
  OPEN cur_rows;
  LOOP
     FETCH cur_rows BULK COLLECT INTO l_reporting_type LIMIT 1000;
     EXIT WHEN l_reporting_type.count = 0;
  
            BEGIN
              FORALL i IN l_reporting_type.FIRST .. l_reporting_type.LAST SAVE EXCEPTIONS
              INSERT INTO reporting_test VALUES (l_reporting_type(i).column_1 .. l_reporting_type(i).column_n);
  
            EXCEPTION
              WHEN ex_dml_errors THEN
                l_error_count := SQL%BULK_EXCEPTIONS.count;
                DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
                FOR i IN 1 .. l_error_count 
                LOOP
                  DBMS_OUTPUT.put_line('Error: ' || i || 
                    ' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
                    ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
                END LOOP;
              END;
  
  END LOOP;
  CLOSE cur_rows;
  END;
  </code></pre>