After a specific event(e.g. someone add new data into the database via a GUI), I need to refresh a materialized view that aggregates some data and only after that refresh is complete I have to query from the MW and to show in the GUI the updated results.
Now, my problem is that I need a way to know when the refresh is complete so that I can then query and be sure to obtain an up to date result.
The MV is specified in this way:
CREATE MATERIALIZED VIEW ASSAY ("TYPE","METHOD",COMPOUND_CORPORATE_ID,RESULTS_AVG,RESULTS_ST_DEV,RESULTS_N) AS select "TYPE","METHOD",AVG("RESULTS"), STDDEV("RESULTS") , COUNT("RESULTS") from ASSAY_INHIB where upper("VALIDATION")='YES' group by "TYPE","METHOD",COMPOUND_CORPORATE_ID; CREATE INDEX assays_inhib_parent ON ASSAY(compound_corporate_id); ALTER MATERIALIZED VIEW ASSAY REFRESH FORCE ON DEMAND;
I call from the GUI a stored procedure with the command exec REFRESH_MV() and the stored procedure is like this :
CREATE PROCEDURE REFRESH_MV () AS BEGIN DBMS_MVIEW.REFRESH('ASSAY'); END REFRESH_MV;
The problem is that I don't know when I can query the MV and be sure that data are up to date. Can anybody help?