troubleshooting Question

Ora-03113: end-of-file on communication channel

Avatar of gs79
gs79 asked on
Oracle Database
6 Comments1 Solution1966 ViewsLast Modified:
I have a view see the code attached: The view works fine..Select * from VIEW retrieves record. But when I use the same view in a merge statement to update/insert a table..I get the following error:

ORA-03113:End of communication channel

Cannot understand why? The view uses "WITH CLAUSE" statment..For sometime I thought using "WITH CLAUSE" may be causing this error. Apparently this is not since I was able to execute the merge statement with another simple view using WITH clause..

Can somebody help me if you have come across the similar problem..

Thanks,
Girish
MERGE
INTO
  DW_REWARD_MNTHLY_AGG DW_REWARD_MNTHLY_AGG
USING
  (SELECT
  TO_NUMBER ( DW_REWARD_MNTHLY_AGG_VW.CALENDAR_MONTH_DW_ID )/* EXPRESSION.OUTGRP1.CALENDAR_MONTH_DW_ID */ CALENDAR_MONTH_DW_ID,
  DW_REWARD_MNTHLY_AGG_VW.INVITED_USER_CNT INVITED_USER_CNT,
  DW_REWARD_MNTHLY_AGG_VW.ENROLLED_USER_CNT ENROLLED_USER_CNT,
  DW_REWARD_MNTHLY_AGG_VW.TRANS_CNRM_MTH_CNT TRANS_CNRM_MTH_CNT,
  DW_REWARD_MNTHLY_AGG_VW.TRANS_CNCL_MTH_CNT TRANS_CNCL_MTH_CNT,
  DW_REWARD_MNTHLY_AGG_VW.TKT_SALE_CNRM_MTH_AMT TKT_SALE_CNRM_MTH_AMT,
  DW_REWARD_MNTHLY_AGG_VW.TKT_SALE_CNCL_MTH_AMT TKT_SALE_CNCL_MTH_AMT,
  DW_REWARD_MNTHLY_AGG_VW.TKT_CNRM_MTH_CNT TKT_CNRM_MTH_CNT,
  DW_REWARD_MNTHLY_AGG_VW.TKT_CNCL_MTH_CNT TKT_CNCL_MTH_CNT,
  DW_REWARD_MNTHLY_AGG_VW.TOT_COST_CNRM_MTH_AMT TOT_COST_CNRM_MTH_AMT,
  DW_REWARD_MNTHLY_AGG_VW.TOT_COST_CNCL_MTH_AMT TOT_COST_CNCL_MTH_AMT,
  DW_REWARD_MNTHLY_AGG_VW.ISSUED_DISCNT_ISSUED_MTH_AMT ISSUED_DISCNT_ISSUED_MTH_AMT,
  DW_REWARD_MNTHLY_AGG_VW.REEDEMED_DISCNT_ISSUED_MTH_AMT REEDEMED_DISCNT_ISSUED_MTH_AMT,
  DW_REWARD_MNTHLY_AGG_VW.DW_CREATED_DTTM DW_CREATED_DTTM,
  DW_REWARD_MNTHLY_AGG_VW.DW_LASTUPDATE_DTTM DW_LASTUPDATE_DTTM
FROM
  DW_REWARD_MNTHLY_AGG_VW  DW_REWARD_MNTHLY_AGG_VW
  )
    MERGE_SUBQUERY
ON (
  DW_REWARD_MNTHLY_AGG.CALENDAR_MONTH_DW_ID = MERGE_SUBQUERY.CALENDAR_MONTH_DW_ID
   )
  
  WHEN NOT MATCHED THEN
    INSERT
      (DW_REWARD_MNTHLY_AGG.CALENDAR_MONTH_DW_ID,
      DW_REWARD_MNTHLY_AGG.INVITED_USER_CNT,
      DW_REWARD_MNTHLY_AGG.ENROLLED_USER_CNT,
      DW_REWARD_MNTHLY_AGG.TRANS_CNRM_MTH_CNT,
      DW_REWARD_MNTHLY_AGG.TRANS_CNCL_MTH_CNT,
      DW_REWARD_MNTHLY_AGG.TKT_SALE_CNRM_MTH_AMT,
      DW_REWARD_MNTHLY_AGG.TKT_SALE_CNCL_MTH_AMT,
      DW_REWARD_MNTHLY_AGG.TKT_CNRM_MTH_CNT,
      DW_REWARD_MNTHLY_AGG.TKT_CNCL_MTH_CNT,
      DW_REWARD_MNTHLY_AGG.TOT_COST_CNRM_MTH_AMT,
      DW_REWARD_MNTHLY_AGG.TOT_COST_CNCL_MTH_AMT,
      DW_REWARD_MNTHLY_AGG.ISSUED_DISCNT_ISSUED_MTH_AMT,
      DW_REWARD_MNTHLY_AGG.REEDEMED_DISCNT_ISSUED_MTH_AMT,
      DW_REWARD_MNTHLY_AGG.DW_CREATED_DTTM,
      DW_REWARD_MNTHLY_AGG.DW_LASTUPDATE_DTTM)
    VALUES
      (MERGE_SUBQUERY.CALENDAR_MONTH_DW_ID,
      MERGE_SUBQUERY.INVITED_USER_CNT,
      MERGE_SUBQUERY.ENROLLED_USER_CNT,
      MERGE_SUBQUERY.TRANS_CNRM_MTH_CNT,
      MERGE_SUBQUERY.TRANS_CNCL_MTH_CNT,
      MERGE_SUBQUERY.TKT_SALE_CNRM_MTH_AMT,
      MERGE_SUBQUERY.TKT_SALE_CNCL_MTH_AMT,
      MERGE_SUBQUERY.TKT_CNRM_MTH_CNT,
      MERGE_SUBQUERY.TKT_CNCL_MTH_CNT,
      MERGE_SUBQUERY.TOT_COST_CNRM_MTH_AMT,
      MERGE_SUBQUERY.TOT_COST_CNCL_MTH_AMT,
      MERGE_SUBQUERY.ISSUED_DISCNT_ISSUED_MTH_AMT,
      MERGE_SUBQUERY.REEDEMED_DISCNT_ISSUED_MTH_AMT,
      MERGE_SUBQUERY.DW_CREATED_DTTM,
      MERGE_SUBQUERY.DW_LASTUPDATE_DTTM)
  WHEN MATCHED THEN
    UPDATE
    SET
                  INVITED_USER_CNT = MERGE_SUBQUERY.INVITED_USER_CNT,
  ENROLLED_USER_CNT = MERGE_SUBQUERY.ENROLLED_USER_CNT,
  TRANS_CNRM_MTH_CNT = MERGE_SUBQUERY.TRANS_CNRM_MTH_CNT,
  TRANS_CNCL_MTH_CNT = MERGE_SUBQUERY.TRANS_CNCL_MTH_CNT,
  TKT_SALE_CNRM_MTH_AMT = MERGE_SUBQUERY.TKT_SALE_CNRM_MTH_AMT,
  TKT_SALE_CNCL_MTH_AMT = MERGE_SUBQUERY.TKT_SALE_CNCL_MTH_AMT,
  TKT_CNRM_MTH_CNT = MERGE_SUBQUERY.TKT_CNRM_MTH_CNT,
  TKT_CNCL_MTH_CNT = MERGE_SUBQUERY.TKT_CNCL_MTH_CNT,
  TOT_COST_CNRM_MTH_AMT = MERGE_SUBQUERY.TOT_COST_CNRM_MTH_AMT,
  TOT_COST_CNCL_MTH_AMT = MERGE_SUBQUERY.TOT_COST_CNCL_MTH_AMT,
  ISSUED_DISCNT_ISSUED_MTH_AMT = MERGE_SUBQUERY.ISSUED_DISCNT_ISSUED_MTH_AMT,
  REEDEMED_DISCNT_ISSUED_MTH_AMT = MERGE_SUBQUERY.REEDEMED_DISCNT_ISSUED_MTH_AMT,
  DW_CREATED_DTTM = MERGE_SUBQUERY.DW_CREATED_DTTM,
  DW_LASTUPDATE_DTTM = MERGE_SUBQUERY.DW_LASTUPDATE_DTTM;
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros