I've created several materialized views, and all but one are working correctly. The specific error I'm getting (via a Java job) is this:
error loading from table 'STG_INTERACTION_RESOURCE_FACT' to table 'R_INTERACTION_RESOURCE_FACT' ORA-12048: error encountered while refreshing materialized view "GIM76"."MED_SEG_FACT_EXT_R" ORA-00904: "MAS$3"."USER_DATA_20": invalid identifier
No errors occur when generating the MV. Refreshing it manually works correctly also:
The error only occurs when the java program attempts to move data from a staging table into the master tables. The MV is quite large, so I'll only post a fragment of the actual query:
CREATE MATERIALIZED VIEW GIM76.MED_SEG_FACT_EXT_R PARALLEL BUILD IMMEDIATE REFRESH ON COMMIT AS select -- other columns elided for brevity coalesce(irf.user_data_1,if.user_data_1) as i_user_data_1, coalesce(irf.user_data_2,if.user_data_2) as i_user_data_2, coalesce(irf.user_data_3,if.user_data_3) as i_user_data_3, coalesce(irf.user_data_4,if.user_data_4) as i_user_data_4, coalesce(irf.user_data_5,if.user_data_5) as i_user_data_5, coalesce(irf.user_data_6,if.user_data_6) as i_user_data_6, coalesce(irf.user_data_7,if.user_data_7) as i_user_data_7, coalesce(irf.user_data_8,if.user_data_8) as i_user_data_8, coalesce(irf.user_data_9,if.user_data_9) as i_user_data_9, coalesce(irf.user_data_10,if.user_data_10) as i_user_data_10, coalesce(irf.user_data_11,if.user_data_11) as i_user_data_11, coalesce(irf.user_data_12,if.user_data_12) as i_user_data_12, coalesce(irf.user_data_13,if.user_data_13) as i_user_data_13, coalesce(irf.user_data_14,if.user_data_14) as i_user_data_14, coalesce(irf.user_data_15,if.user_data_15) as i_user_data_15, coalesce(irf.user_data_16,if.user_data_16) as i_user_data_16, coalesce(irf.user_data_17,if.user_data_17) as i_user_data_17, coalesce(irf.user_data_18,if.user_data_18) as i_user_data_18, coalesce(irf.user_data_19,if.user_data_19) as i_user_data_19, coalesce(irf.user_data_20,if.user_data_20) as i_user_data_20, -- other columns elided msf.rowid as msf_rid, if.rowid as if_rid, cust.rowid as cust_rid, vrf.rowid as vrf_rid, irf.rowid as irf_rid from gim76.r_mediation_segment_fact msf, gim76.r_interaction_fact if, gim76.customer cust, gim76.r_voice_res_fact_ext vrf, gim76.r_interaction_resource_fact irf where msf.interaction_id = if.interaction_id and msf.target_ixn_resource_id = irf.interaction_resource_id (+) and cust.customer_key (+) = irf.customer_key and irf.ixn_res_fact_ext_key = vrf.voice_res_fact_ext_key (+);
I can guarantee that the column
user_data_20 exists in both tables (
irf). This should be obvious since the MV generates fine, and updates manually fine.
Am I missing something here? I don't understand the
MAS$3 syntax, of what that refers to.
I'm creating another MV that uses very similar columns. Instead of doing a coalesce on the troublesome column though, it just uses the value from the first table. This one works correctly.
CREATE MATERIALIZED VIEW GIM76.IRF_EXT_R PARALLEL BUILD IMMEDIATE REFRESH ON COMMIT AS select -- other columns irf.user_data_1 as user_data_1, irf.user_data_2 as user_data_2, irf.user_data_3 as user_data_3, irf.user_data_4 as user_data_4, irf.user_data_5 as user_data_5, irf.user_data_6 as user_data_6, irf.user_data_7 as user_data_7, irf.user_data_8 as user_data_8, irf.user_data_9 as user_data_9, irf.user_data_10 as user_data_10, irf.user_data_11 as user_data_11, irf.user_data_12 as user_data_12, irf.user_data_13 as user_data_13, irf.user_data_14 as user_data_14, irf.user_data_15 as user_data_15, irf.user_data_16 as user_data_16, irf.user_data_17 as user_data_17, irf.user_data_18 as user_data_18, irf.user_data_19 as user_data_19, irf.user_data_20 as user_data_20, -- other columns irf.rowid as irf_rid, if.rowid as if_rid, cust.rowid as cust_rid, vrf.rowid as vrf_rid from gim76.r_interaction_resource_fact irf, gim76.r_interaction_fact if, gim76.customer cust, gim76.r_voice_res_fact_ext vrf where irf.interaction_id = if.interaction_id and irf.customer_key = cust.customer_key and irf.ixn_res_fact_ext_key = vrf.voice_res_fact_ext_key (+);
Could the problem be that multiple MVs are created on the same tables/fields? I couldn't see how that could have anything to do with it, but I'm merely a developer getting his dba feet wet at the moment.
It appears the order of the coalescing statement matters in my particular case. If I reverse the order of the fields in the coalesce statement, it appears to work correctly. It definitely works if I remove the coalesce altogether.
Is there some kind of race condition that may be the cause of this? I find it extremely strange.