One Comment on “Oracle Materialized View Manual Refresh”

1. 

   Reply

   Sanjay J. Khachane

   12.09.2009 at 8:41 am

   Dear all,
   This is Code For Refresh Materialized in .NET
   Public Sub RefreshMView()
   Dim tran As OracleTransaction
   Dim Con As String = “Data Source=” & ServerName & “;User ID=” & UserName & “;Password=” & Pwd & “;Unicode=True”
   Dim Conn As New OracleConnection(Con)
   Try
   Conn.Open()
   tran = Conn.BeginTransaction
   Dim cmd As New OracleCommand()
   cmd.Connection = Conn
   cmd.Transaction = tran
   cmd.CommandType = CommandType.StoredProcedure
   cmd.CommandText = “refresh_P_V_PO_QTY”
   cmd.ExecuteNonQuery()
   tran.Commit()
   Conn.Close()
   cmd = Nothing
   Catch ex As Exception
   tran.Rollback()
   Finally
   Conn.Close()
   End Try
   End Sub

   //Procedure in ORACLE For Refresh Materialized View in VB.NET
   CREATE PROCEDURE refresh_P_V_PO_QTY
   IS
   v_error_code NUMBER;
   v_error_message VARCHAR2 (400);
   v_date DATE;
   BEGIN
   /*Materialized 1*/
   dbms_mview.refresh(‘V_SCH_QTY’,’C’);
   COMMIT;
   /*Materialized 2*/
   dbms_mview.refresh(‘V_DISP_QTY’,’C’);
   COMMIT;
   /*Materialized 3*/
   dbms_mview.refresh(‘V_PO_QTY’,’C’);
   COMMIT;
   /*Materialized 4*/
   dbms_mview.refresh(‘V_PO_DETAIL’,’C’);
   COMMIT;
   EXCEPTION
   WHEN OTHERS
   THEN
   v_error_code := SQLCODE;
   v_error_message := SUBSTR (SQLERRM, 1, 100);
   DBMS_OUTPUT.put_line ( ‘mv_opbyiss_temp_or:-‘
   || ‘ Date:’
   || v_date
   || ‘ ‘
   || v_error_code
   || ‘ ‘
   || v_error_message
   );
   END;