"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>

Answer 2

One of the differences between FAST and COMPLETE refresh is that the refresh process does a more complex "parsing" in order to fast refresh values in particular columns depending on which source tables have been changed and so on. One of the reasons why ROWID has to be included as part of the mview columns so it can find the correct rows to change column values in.

One of the differences between version 10 and version 11 I believe is, that when you create long expressions as columns, version 10 would give the column a SYSxxxxxxxx name, while version 11 attempts to keep the expression as the column name.

Your query is something like:

SELECT UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR (History, 2000, 1)),
       UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR (AnotherCol, 2000, 1)),
       ...

It is possible (can't test it so I am not completely sure) that what you are actually seeing is something going wrong in the handling of the metadata about the mview when the column names are such very long expressions.

Have you seen the column names of the created mview? Are they long and complex expressions? If yes, then I suggest trying to use column aliases:

SELECT UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR (History, 2000, 1)) AS col1,
       UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR (AnotherCol, 2000, 1)) AS col2,
       ...

(Well, you can use more meaningful names than col1, col2 of course ;-)