Hi,
I have two schema "UserA" and "UserB" . "UserA" has a function which is part of a package. There is a "role" , that's given execute permission on the package. The role is granted to UserB. UserB can use it in SELECT statements. But when using the same select statement to create a materialized view, it throws an error "ORA-00942: table or view does not exist" .
So the select statement works fine. the function get_address_details of the package m_lookup returns result as expected.
SELECT name, m_lookup.get_address_details( address_id) address_details FROM UserA.customers
but when the same select statement is used in the materialized view definition, it throws error
CREATE MATERIALIZED VIEW mv_customer_data
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
DISABLE QUERY REWRITE AS
SELECT name, m_lookup.get_address_details( address_id) address_details FROM UserA.customers
ORA-00942: table or view does not exist
Any suggestions to resolve this ?
Thanks