Hi All,
We have a procedure in backend which refreshes 3-4 MV's.If we call this procedure from ODI it gives the error :
ODI-1228: Task ABC_PRC_REFRESH_MV (Procedure) fails on the target ORACLE connection ABC_P_SR.
Caused By: java.sql.SQLException: ORA-12048: error encountered while refreshing materialized view "APPS"."MTL_IT_MV"
ORA-12057: materialized view "APPS"."MTL_IT_MV" is INVALID and must complete refresh
If we execute the procedure code from backend it completes successfully.
Procedure definition is:
PROCEDURE ABC_PRC_REFRESH_MV(
p_mview_name IN VARCHAR2
)
IS
p_list VARCHAR2(2000) := NULL;
p_method VARCHAR2(1) := '?';
p_rollback_seg VARCHAR2(10) := NULL;
p_push_deferred_rpc BOOLEAN := true;
p_refresh_after_errors BOOLEAN := false;
p_purge_option BINARY_INTEGER := 1;
p_parallelism BINARY_INTEGER := 0;
p_heap_size BINARY_INTEGER := 0;
p_atomic_refresh BOOLEAN := true;
BEGIN
p_list := 'MTL_IT_MV,MTL_RES_TXN_IT_MV,MTL_RES_TXN_LT_MV';
IF(p_mview_name = 'ALL')
THEN
DBMS_MVIEW.REFRESH(p_list, p_method, p_rollback_seg, p_push_deferred_rpc,
p_refresh_after_errors, p_purge_option,
p_parallelism, p_heap_size, p_atomic_refresh);
ELSE
DBMS_MVIEW.REFRESH(p_mview_name,p_method);
END IF;
END ABC_PRC_REFRESH_MV;
When this procedure was called form ODI it gave the error mentioned above.
After that we executed the below statements form backend:
exec DBMS_MVIEW.REFRESH('MTL_IT_MV','?','',FALSE,FALSE,0,0,0,TRUE);
exec DBMS_MVIEW.REFRESH('MTL_RES_TXN_IT_MV','?','',FALSE,FALSE,0,0,0,TRUE);
exec DBMS_MVIEW.REFRESH('MTL_RES_TXN_LT_MV','?','',FALSE,FALSE,0,0,0,TRUE);
these statements completed successfully .If now I run the scenario its completing successfully.
Doe anyone have any idea why this scenario is failing when its run for the first time.Because when the statements are run from backend they successfully complete and also after that if this scenario is run then it also completes successfully.
Thanks,
Amrit