title: "too many declarations of 'SUBSTR' match this call" while refreshing Materialized view
body: <p>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:</p>
  
  <pre><code>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
         &lt;3 more blob columns with similar conversions&gt;,
         &lt;misc columns from different tables&gt;,
         &lt;rowid columns for tables for REFRESH FAST to work&gt;
  FROM   &lt;list of tables with JOINs&gt;
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.) </p>
  
  <p>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.</p>
  
  <p>What am I missing here?</p>
  
  <p>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.</p>
  
  <p>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. </p>
  
  <p>So, there is some issue with DBMS_LOB.SUBSTR dealing with BLOBs such that:</p>
  
  <ol>
  <li>What runs in COMPLETE refresh doesn't run correctly in FAST refresh.</li>
  <li>What runs in Oracle 10.2.0.4 doesn't run in 11.1.0.7.</li>
  </ol>
  
  <p>How do I further troubleshoot this?</p>
  
  <p>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.</p>