"too many declarations of 'SUBSTR' match this call" while refreshing Materialized view

Question

I have a Materialized View set to REFRESH FAST ON COMMIT. There are 4 BLOB columns from the source table that I'm converting to VARCHAR2 as part of the MV:

CREATE MATERIALIZED VIEW Employee_MV
REFRESH FAST ON COMMIT
WITH PRIMARY KEY
AS
SELECT UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR (History, 2000, 1)), //BLOB column
       <3 more blob columns with similar conversions>,
       <misc columns from different tables>,
       <rowid columns for tables for REFRESH FAST to work>
FROM   <list of tables with JOINs>

If the MV is refreshed while inserting rows in the participating tables with the BLOB columns - be it via ON COMMIT or ON DEMAND – it errors out with the following message:

ORA-12008: error in materialized view refresh path
ORA-06553: PLS-307: too many declarations of 'SUBSTR' match this call
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2545
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2751
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2720
ORA-06512: at line 1

What confounds me is that the error is at "DBMS_LOB.SUBSTR" call. (That is the only place I've used SUBSTR in this MV, and if I remove the BLOB conversions the MV refreshes without errors.)

Does this mean Oracle is unable to resolve to the correct overloaded version (there is one SUBSTR each for CLOB, BLOB and BFILE)? But that doesn't make sense, because if I run the SELECT query of this MV separately it runs just fine.

What am I missing here?

UPDATE: I tried refreshing the MV with REFRESH COMPLETE option and it worked, with the same data. So now I have a situation where the SUBSTR() is failing only for FAST refreshes, but works for COMPLETE refreshes.

UPDATE 2: The current Oracle version is 11.1.0.7. I tried running this same MV in Oracle 10.2.0.4 (a different environment). The MV completed FAST REFRESH without any issues.

So, there is some issue with DBMS_LOB.SUBSTR dealing with BLOBs such that:

1. What runs in COMPLETE refresh doesn't run correctly in FAST refresh.
2. What runs in Oracle 10.2.0.4 doesn't run in 11.1.0.7.

How do I further troubleshoot this?

UPDATE 3: I just ran some more tests to check if presence of NULL in BLOB columns is required for such behavior - it turns out that even with non-null values, MV FAST REFRESH fails with same error. I've updated the question accordingly.

Answer 1

If Oracle doesn't let you run so many times "substr" function in that mv - do some trick to cheat him ;) You can make this "substr" calls before fast refresh by using virtual column like that:

alter table YOUR_TABLE add History_substr as (DBMS_LOB.SUBSTR (History,2000, 1)) virtual;

do here other "3 more blob columns with similar conversions" as above and then you can use your virtual columns:

CREATE MATERIALIZED VIEW Employee_MV
REFRESH FAST ON COMMIT
WITH PRIMARY KEY
AS
SELECT UTL_RAW.CAST_TO_VARCHAR2(History_substr), //BLOB column
       <3 more blob columns with similar conversions>,
       <misc columns from different tables>,
       <rowid columns for tables for REFRESH FAST to work>
FROM   <list of tables with JOINs>