"too many declarations of 'SUBSTR' match this call" while refreshing Materialized view [message #628160] |
Thu, 20 November 2014 10:56 |
|
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 #628163 is a reply to message #628162] |
Thu, 20 November 2014 11:48 |
|
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 #628308 is a reply to message #628302] |
Sun, 23 November 2014 13:08 |
Solomon Yakobson
Messages: 3273 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.
|
|
|