Hi,
I have a requirement to refresh a materialised view in Oracle database, read the view and then write to a CSV file.
Reading of the view should only happen after the view is refreshed.
Tried using 2 SQLCreators
First one,
Begin
DBMS_MVIEW.REFRESH(SCHEMA.MVIEW_NAME);
End;
Second One,
Select * from SCHEMA.MVIEW_NAME;
How to make sure that Materialised View will be read only after the view is refreshed?
Thanks
sresree
Hi @sresree, instead of 2 SQLCreators, you could use an SQLCreator for the refresh and then connect it to an SQLExecutor to perform the query.
Hi @pflegpet , Thank you for the reply. I will try that. But will it run in sequence?
When chaining two SQLExecutors together, the second won't execute before the first one has terminated. In this context it does not necessarily mean that the mview has completed the refresh, only that the Oracle back-end has acknowledged the demand for a refresh.
You can query Oracle for the date of the last completed refresh before progressing, this could e.g. be done in a looping custom transformer:
SELECT LAST_REFRESH_TYPE, SYSDATE - LAST_REFRESH_DATE AS LAST_REFRESH_AGE
FROM ALL_MVIEWS
WHERE OWNER = 'MY_SCHEMA' AND MVIEW_NAME = 'MY_MVIEW';You can then check that LAST_REFRESH_AGE is e.g. less than 1 (day) before continuing.
You can use the Decelerator transformer to give Oracle some pause between repeated requests.
(Untested) An alternative may be to use an Oracle reader, and refresh the materialized view in the SQL To Run Before parameter (Parameters, Advanced).