Job
Ghassan, June 21, 2016 - 1:04 pm UTC
Create a job or what else plsql sub-programs ..
Get the count
..If count ... then dbms redress. . End if;
Sometimes the simplest things didn't go up to mind.
June 21, 2016 - 2:00 pm UTC
I'm not following you. You mean have a job in the background that periodically checks and refreshes the MV if required?
You could do this. It's not clear what the requirement is though. If it's "simulate refresh on commit, but only for inserts" then the job approach will likely miss some data.
If it's "refresh whenever, but only refresh when there's data in the table", then yes, the job does the trick.
Followup
Ghassan, June 21, 2016 - 2:57 pm UTC
Yes seem that the requirements above fit with in the very last phrase.
But indeed no clear demand no 100 % adequate response .
Santhosh Kumar, June 28, 2016 - 12:09 pm UTC
Thanks for the reply ,
i have written small code to refresh based on condition, but im facing difficulty as im getting errors.
Below is the code which i written and calling it to refresh based on condition
DECLARE
a number(10) := 0;
BEGIN
-- check the boolean condition using if statement
select count(1) INTO a from CDWMARTMANUFACTURING.DIM_RAW_WAFER_MATERIAL;
IF( a > 0 ) THEN
DBMS_MVIEW.refresh('CDWMARTMANUFACTURING.MV_DIM_RAW_WAFER_SFDC');
END IF;
END;
Can you kindly help to know what is the issue in the above piece of code.
Thanks
Santhosh
June 29, 2016 - 3:28 am UTC
When you say "i'm getting errors" and then dont tell us what the errors are, this is like us saying:
"I've got a favourite colour. Please tell me what it is"
You need to help us to help you.