Home » SQL & PL/SQL » SQL & PL/SQL » "too many declarations of 'SUBSTR' match this call" while refreshing Materialized view (Oracle 11.1.0.7)
"too many declarations of 'SUBSTR' match this call" while refreshing Materialized view [message #628160] Thu, 20 November 2014 10:56 Go to next message
kskarun
Messages: 4
Registered: November 2014
Junior Member
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 having NULL values - 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 of SUBSTR if the input is NULL? But that doesn't make sense, because if I run the SELECT query of this MV separately it runs just fine, NULLs and all.

Here are the things I have tried so far:
1. I tried refreshing the MV with REFRESH COMPLETE option and it worked fine, with the same data.
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?
Re: "too many declarations of 'SUBSTR' match this call" while refreshing Materialized view [message #628162 is a reply to message #628160] Thu, 20 November 2014 11:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How do you know this is the first one that raises the error?
Do NOT tell us, SHOW us. Use SQL*Plus and copy and paste your session.


Re: "too many declarations of 'SUBSTR' match this call" while refreshing Materialized view [message #628163 is a reply to message #628162] Thu, 20 November 2014 11:48 Go to previous messageGo to next message
kskarun
Messages: 4
Registered: November 2014
Junior Member
Can you please tell me what you refer to as "the first one that raises the error"? Are you asking if this is the first query that gives the error? I am doing this in the DEV environment, and I am / my application is the only one using this schema and running queries.

I get this error in two ways:

1. I do an insert to the master table(s) via a .NET application. When the application commits the transaction, this exception is thrown. There is only one MV in the entire data setup, and that is involved in this transaction.
2a. I change ON COMMIT to ON DEMAND, run the application (it now commits without errors); then go to TOAD, run DBMS_MVIEW.REFRESH('Employee_MV'); at that point, this error gets thrown as a TOAD dialog box (which I've copied and reproduced).
2b. Now if I do a DBMS_MVIEW.REFRESH('Employee_MV', 'C'), the MV refreshes without any errors.

I'm able to repeat this scenario any number of times.

Have I understood your question?

[Updated on: Thu, 20 November 2014 11:59]

Report message to a moderator

Re: "too many declarations of 'SUBSTR' match this call" while refreshing Materialized view [message #628187 is a reply to message #628163] Fri, 21 November 2014 00:51 Go to previous messageGo to next message
kskarun
Messages: 4
Registered: November 2014
Junior Member
UPDATE: It looks like presence of NULL values on BLOB is not a requirement for this behavior. I tried inserting non-null values on all 4 BLOB columns (they were set to NULL by default by the application, so I'd assumed this was the cause). But even with non-null values for BLOBs, I get the same error for FAST REFRESH in 11.1.0.7.

To summarize:

1. Materialized View with UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR()) function call fails with "PLS-307: too many declarations of 'SUBSTR' match this call".
2. This happens only during FAST REFRESH, and only on Oracle 11.1.0.7.
3. This works fine for COMPLETE REFRESH in 11g, and for both FAST and COMPLETE refresh in 10g.

How do I figure out what the issue is?
Re: "too many declarations of 'SUBSTR' match this call" while refreshing Materialized view [message #628188 is a reply to message #628187] Fri, 21 November 2014 00:58 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Check My Oracle Support? Maybe it is described there.
Re: "too many declarations of 'SUBSTR' match this call" while refreshing Materialized view [message #628302 is a reply to message #628160] Sun, 23 November 2014 10:09 Go to previous messageGo to next message
kskarun
Messages: 4
Registered: November 2014
Junior Member
While I couldn't figure out this particular problem, I solved my REFRESH FAST issue by adopting a workaround.

I created 4 extra columns in the table that had the BLOB columns, and wrote a trigger that'll do conversion from BLOB to VARCHAR2. I used these columns in the MV instead of the BLOB columns, thereby eliminating the error condition.
Re: "too many declarations of 'SUBSTR' match this call" while refreshing Materialized view [message #628308 is a reply to message #628302] Sun, 23 November 2014 13:08 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Try:

CREATE MATERIALIZED VIEW Employee_MV
REFRESH FAST ON COMMIT
WITH PRIMARY KEY
AS
SELECT UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(TO_BLOB(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>


For example:

SCOTT@orcl > select dbms_lob.substr(null,1,1) from tbl;
select dbms_lob.substr(null,1,1) from tbl
       *
ERROR at line 1:
ORA-06553: PLS-307: too many declarations of 'SUBSTR' match this call


SCOTT@orcl > select dbms_lob.substr(to_blob(null),1,1) from tbl;

DBMS_LOB.SUBSTR(TO_BLOB(NULL),1,1)
--------------------------------------------------------------------------------



SCOTT@orcl > 


SY.
Previous Topic: oracle xml
Next Topic: Oracle Regular Expression
Goto Forum:
  


Current Time: Thu Mar 28 16:02:24 CDT 2024