“too many SUBSTR declarations match this challenge” when covering a materialized view

Question

I have a Materialized View set for REFRESH FAST ON COMMIT. There are 4 BLOB columns from the source table that I convert to VARCHAR2 as part of 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 MV is updated when inserting rows into the participating tables with BLOB columns - whether through ON COMMIT or ON DEMAND - it displays a message 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 scares me is that the error is related to calling DBMS_LOB.SUBSTR. (This is the only place I used SUBSTR in this MV, and if I remove the BLOB conversions, MV will update without errors.)

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

What am I missing here?

UPDATE: I tried updating MV using the REFRESH COMPLETE option, and it worked with the same data. So, now I have a situation where SUBSTR () does not work only for FAST updates, but works for COMPLETE updates.

UPDATE 2: The current version of Oracle is 11.1.0.7. I tried to run the same MV in Oracle 10.2.0.4 (in a different environment). MV completed FAST REFRESH without any problems.

So there is some problem with DBMS_LOB.SUBSTR related to BLOB, so that:

• What works when updating COMPLETE does not work correctly in updating FAST.
• What works in Oracle 10.2.0.4 does not work in 11.1.0.7.

How to fix this problem?

UPDATE 3: I just ran a few more tests to check for the presence of NULL in the BLOB columns required for this behavior - it turns out that even with nonzero values, MV FAST UPDATE fails with the same error. I updated the question accordingly.

Answer 1

If Oracle does not allow you to run so many times “substr” in that mv is to do some trick to trick it;) You can make these calls to “substr” before a quick update using a virtual column:

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

follow here the other “3 more blob columns with similar conversions” as described 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 updating FAST and COMPLETE is that the update process performs a more sophisticated “parsing” to quickly update values ​​in specific columns depending on which source tables have been changed and so on. One of the reasons ROWID should be included as part of mview columns is why it can find the right rows to change the column values.

One of the differences between version 10 and version 11, I believe, is that when creating long column expressions, version 10 gives the column the name SYSxxxxxxxx, and version 11 tries to save the expression as the column name.

Your query looks something like this:

 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 (I can’t check it, so I'm not quite sure) that what you actually see is something wrong with the processing of mview metadata when the column names are such very long expressions.

Have you seen the column names created by mview? Are they long and complex expressions? If so, then I suggest using 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 ;-)