Materialized view calling pl/sql table function
Posted on 2004-10-20
I have an issue.
I have created a materialized view which calls a plsql table function to populate itself.
Here is the code:-
create materialized view data_gmsone.wasd_spells
refresh complete next trunc(sysdate) + 1
select /*+ use_hash(a) */ nino$1 ,
from data_gmsone.data_gmsone_cust_ben_dets a
where s$delflag = 'I'
and (MAX_CLAIM_DATE$1 is null or MAX_CLAIM_DATE$1 > '28-JUN-1999')
and BENEFIT$1 in ('JUV', 'IS', 'IB', 'SDA', 'ICA', 'WB', 'BB' , 'PC')
and CLAIM_START_DATE$1 is not null
and CUSTOMER_TYPE$1 = 'C'
order by nino$1, claim_start_date$1)
It creates fine, but the dbms_job refresh that gets created fails with
control@assist_o> exec dbms_refresh.refresh('"DATA_GMSONE"."WASD_SPELLS"');
BEGIN dbms_refresh.refresh('"DATA_GMSONE"."WASD_SPELLS"'); END;
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
I dont understand this. The source tables exist. I have granted execute on the table function to the owner of the materialized view. The materialized view works when I create it, but doesnt work on a refresh.!!! I have tried the refresh under several users including the owner and a dba, and get the same error message for all of them.