Materialized View with SDO_GEOMETRY column based on PL/SQL function
I have the following problem - it is known that materialized view wants PL/SQL functions used in it to be DETERMINISTIC. And it appears that a function which returns SDO_GEOMETRY cannot be DETERMINISTIC - I can add DETERMINISTIC modifier to my function which returns sdo_geometry based on USNG grid ID (I know there is Oracle's function to do the same, but it is very slow) and save the package, and it compiles and runs fine with regular queries, but when it comes to materialized view (mview), the following error is thrown:
ORA-12018: following error encountered during code generation for "SCHEMA"."MVIEW_NAME"
ORA-00932: inconsistent datatypes: expected NUMBER got MDSYS.SDO_GEOMETRY
Looks like DETERMINISTIC modifier is not fully supported for object types. I have tried to use SDO_CS.FROM_USNG Oracle's function, and it appeared that this function is also non-deterministic - I cannot refresh mview with P or F on-demand refresh method (see http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14223/refresh.htm#i1008349 for a list of on-demand refresh methods). Without that function I can refresh mview with P or F flags.