Skip to Main Content
  • Questions
  • Exception handling -What part of Query is Failing

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, krishnaveni.

Asked: November 14, 2022 - 10:24 pm UTC

Last updated: November 22, 2022 - 5:00 pm UTC

Version: 1.1

Viewed 100+ times

You Asked

Hi Tom,Can you please help me in the below issue Please?
I have code that is causing an issue during run time,but i dont know how to write exception handling for the same.
Ex. There is a view(v1) on a table and another view(v2) in which has some business logic.There is a MV in which V! and V2 are being referred and when im trying to refresh this MV its failing with and error

ORA-12008: error in materialized view or zonemap refresh path
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3020
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2432
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 88
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 253
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2413
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2976
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3263
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3295
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 16
ORA-06512: at "DWCOMMON.DWANALYTICS_PKG", line 2395
ORA-06512: at line 1
ORA-06512: at "INTERFACE_ORCH.EXECUTE_DAILY_JOBS_PKG", line 24


I really dont get what is cause of failure as this doesn't include root cause of the error.

The same error message i get for any MV refresh failure.

ORA-12008: error in materialized view or zonemap refresh path
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3020
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2432
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 88
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 253
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2413
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2976
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3263
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3295
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 16
ORA-06512: at "DWCOMMON.DWCOMMON_PKG", line 2344
ORA-06512: at line 1
ORA-06512: at "INTERFACE_ORCH.EXECUTE_DAILY_JOBS_PKG", line 24


It will allways gives me the same generic Error

and Chris said...

As you say, this a fairly generic error.

Things to check:

- What is the query in the MV? Do you get any errors when running this directly (i.e. NOT in the MV)?
- Are you attempting to fast refresh for the MV? If so, is there a materialized view log on all the underlying tables?
- Pass it to dbms_mview.explain_mview to see what types of refresh are possible:

/*
-- create this table if not present
create table mv_capabilities_table (
  statement_id      varchar(30) ,
  mvowner           varchar(30) ,
  mvname            varchar(30) ,
  capability_name   varchar(30) ,
  possible          character(1) ,
  related_text      varchar(2000) ,
  related_num       number ,
  msgno             integer ,
  msgtxt            varchar(2000) ,
  seq               number
);
truncate table mv_capabilities_table ;
*/
exec dbms_mview.explain_mview('YOUR_MV');
select capability_name, possible, msgtxt from mv_capabilities_table ;

Rating

  (2 ratings)

Comments

Krishna, November 21, 2022 - 3:40 pm UTC

Hi Chris,
Thanks for your response.
We ran trace file and figured out that there is an issue during implicit date conversion. When we verified its giving us a invalid month exception. To My surprise when i call the procedure that refreshes this MV from Informatica it gives us the invalid month in the error stack; but when i insert a record in a table when exception raised which is in another schema INTERFACE_ORCH(even the exception table also in the same INTERFACE_ORCH) isnt really catching the exception.
Could you please let me know if we insert the exception record that is raised in DWCOMMON schema in another schema INTERFACE_ORCH by using a procedure inside INTERFACE_ORCH can cause this issue of swallowing?

Thanks,
Krishna.
Chris Saxon
November 22, 2022 - 5:00 pm UTC

I'm lost as to what all those schemas are! We'll need a test case (create table + inserts + select statements) to help effectively.

But it's possible you have an implicit string<>date conversion somewhere. These depends on the client's NLS date settings. Different clients can have different formats! (e.g. DD-MON-YYYY, YYYY/MM/DD)

So it's entirely possible that the query "works on your machine" but fails in production.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.