Solved

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

Posted on 2011-09-06
6
1,597 Views
Last Modified: 2012-06-27
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;

Open in new window

0
Comment
Question by:gs79
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 

Author Comment

by:gs79
ID: 36492130
here is the view query:
with
inv as
(
select month_dw_id, count(reward_user_id) invited_user_cnt
from reward_user ru, dw_month_dim mth
where invited_tier_id is not null
and to_char( ru.created_date,'YYYY/MM') = mth_yr
and trunc(ru.created_date) between to_date('2011-06-01','YYYY-MM-DD') and  to_date('2011-07-31','YYYY-MM-DD')
group by month_dw_id
),

--enrolled_user count
enr as
(
select month_dw_id, count(user_id) enrolled_user_cnt 
from 
reward_user ru, reward_status rs, dw_month_dim mth
where RU.REWARD_STATUS_ID = RS.REWARD_STATUS_ID
and to_char(enrolled_date,'YYYY/MM') = mth_yr
and trunc(enrolled_date) between to_date('2011-06-01','YYYY-MM-DD') and  to_date('2011-07-31','YYYY-MM-DD')
and rs.reward_status_id =20--in( 20,30)
group by month_dw_id
),
cnfm as
(
--trans_confirm mnth count, -tkt_sale_cnrm_mnth_amnt, ticket_cost_cnrm_mth_amt
select month_dw_id, count(1) TRANS_CNRM_MTH_CNT, sum(ticket_cost) TKT_SALE_CNRM_MTH_AMT, 
sum(quantity) TKT_CNRM_MTH_CNT, sum(total_cost) TOT_COST_CNRM_MTH_AMT
from dw_sales_pipeline_fact spf,
dw_users_dim usr,
reward_user b, 
dw_dates_dim c,
dw_month_dim e
where spf.buyer_dw_id = usr.user_dw_id
and usr.user_id = B.USER_ID
and SPF.CONFIRM_DT_DW_ID = c.day_dw_id
and to_char(c.dt_date,'YYYY/MM') =e.mth_yr
and c.dt_date between to_date('2011-06-01','YYYY-MM-DD') and   to_date('2011-08-31','YYYY-MM-DD')
group by month_dw_id
--trans_cncl_mth_cnt tkt_sale_cncl_mnth_amnt, ticket_cost_cncl_mth_amt
),
cncl as
(
select month_dw_id, count(1) TRANS_CNCL_MTH_CNT, sum(ticket_cost) TKT_SALE_CNCL_MTH_AMT, 
sum(quantity) TKT_CNCL_MTH_CNT, sum(total_cost) TOT_COST_CNCL_MTH_AMT
from dw_sales_pipeline_fact spf,
dw_users_dim usr,
reward_user b, 
dw_dates_dim c,
dw_month_dim e
where spf.buyer_dw_id = usr.user_dw_id
and usr.user_id = B.USER_ID
and SPF.CANCEL_DT_DW_ID = c.day_dw_id
and to_char(c.dt_date,'YYYY/MM') =e.mth_yr
and c.dt_date between to_date('2011-07-01','YYYY-MM-DD') and   to_date('2011-07-31','YYYY-MM-DD')
group by month_dw_id
),
dis as(
   SELECT month_dw_id,
   sum(CAST (NVL (D.AMOUNT, 0) AS NUMBER (10, 2))) AS ISSUED_DISCNT_ISSUED_MTH_AMT,
   sum(CAST ( (D.AMOUNT - NVL (UD.AMOUNT_USED, 0)) AS NUMBER (10, 2))) as redeemed_discnt_issued_amt
        FROM reward_user ru,
          discounts d,
          (  SELECT SUM (ud.amount_used) amount_used,
                    MAX (ud.date_added) date_added,
                    d.user_id,
                    d.id discounts_id
               FROM discounts d, used_discounts ud
              WHERE d.id = ud.discount_id(+) AND d.currency_code = 'USD'
           GROUP BY d.user_id, d.id) ud,
          promotional_codes pc,
          promotional_code_groups pcg,
          promotional_code_source pcs,
          discount_types dt,
          dw_currency_lookup cc,
          dw_month_dim mth
    WHERE     ru.user_id = D.USER_ID
          AND ud.user_id = d.user_id
          AND d.id = ud.discounts_id
          AND d.promotional_code_id = pc.id
          AND pc.PROMOTIONAL_CODE_GROUP_ID = pcg.id
          AND pcg.promotional_code_source_id = pcs.promotional_code_source_id
          AND d.discount_type_id = dt.id
          and to_char( ru.created_date,'YYYY/MM') = mth_yr
          and trunc(ru.created_date) between to_date('2011-06-01','YYYY-MM-DD') and  to_date('2011-07-31','YYYY-MM-DD')
          AND cc.currency_code = d.currency_code
          and trunc(d.date_added) between to_date('2011-06-01','YYYY-MM-DD') and to_date('2011-08-31','YYYY-MM-DD') 
          --and discounts_id = 860539
          --and discounts_id =860308-- '855788'
          AND ru.enrolled_date = (SELECT MAX (enrolled_date)
                                    FROM reward_user r
                                   WHERE r.user_id = ru.user_id)
          AND pc.active = 1
          AND pc.active = 1
          AND pcs.active = 1
          AND pcs.PROMOTIONAL_CODE_SOURCE = 'REWARDS'
          group by month_dw_id
          )
select nvl(nvl(nvl(inv.month_dw_id, enr.month_dw_id),cnfm.month_dw_id),cncl.month_dw_id) calendar_month_dw_id,
nvl(inv.INVITED_USER_CNT,0) INVITED_USER_CNT, 
nvl(enr.ENROLLED_USER_CNT,0) ENROLLED_USER_CNT,
nvl(cnfm.TRANS_CNRM_MTH_CNT,0) TRANS_CNRM_MTH_CNT,
nvl(cncl.TRANS_CNCL_MTH_CNT,0) TRANS_CNCL_MTH_CNT,
nvl(TKT_SALE_CNRM_MTH_AMT,0) TKT_SALE_CNRM_MTH_AMT,
nvl(TKT_SALE_CNCL_MTH_AMT,0) TKT_SALE_CNCL_MTH_AMT,
nvl(cnfm.TKT_CNRM_MTH_CNT,0) TKT_CNRM_MTH_CNT,
nvl(cncl.TKT_CNCL_MTH_CNT,0)TKT_CNCL_MTH_CNT,
nvl(cnfm.TOT_COST_CNRM_MTH_AMT,0)TOT_COST_CNRM_MTH_AMT,
nvl(cncl.TOT_COST_CNCL_MTH_AMT,0) TOT_COST_CNCL_MTH_AMT,
nvl(ISSUED_DISCNT_ISSUED_MTH_AMT, 0) ISSUED_DISCNT_ISSUED_MTH_AMT,
nvl(redeemed_discnt_issued_amt,0)reedemed_discnt_issued_mth_amt,
sysdate dw_created_dttm, sysdate dw_lastupdate_dttm
from enr full outer join cnfm ON (enr.month_dw_id = cnfm.month_dw_id)
full outer join cncl on (nvl(enr.month_dw_id, cnfm.month_dw_id) = cncl.month_dw_id)
full outer join inv on  nvl(nvl(enr.month_dw_id, cnfm.month_dw_id),cncl.month_dw_id) = inv.month_dw_id
full outer join dis on  nvl(nvl(enr.month_dw_id, cnfm.month_dw_id),cncl.month_dw_id) = dis.month_dw_id

Open in new window

0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 36492160
This is sort of a generic error.  I have seen data itself cause this error.

You should open an SR with Oracle Support on this one.  They are real hard to track down.
0
 

Author Comment

by:gs79
ID: 36493000
though this error is whimsical, I think "FULL OUTER JOIN" clause is causing this error when the view is used in the merge construct. I tested this by creating a view by joining two small tables..

When I did a full outer join using traditional way ie two left and right outer joins combined with union clause as shown below and then using this in a merge construct DID NOT throw any error and worked fine..while this simple view using FULL OUTER JOIN threw the same error..
Now how do I convert my actual query listed above using combination of LEFT / RIGHT Outer join?


create or replace force test_Vw as
with
a as
(select id,nm from te),
b as
(select nm, addr from te_lkp) 
 select id, nvl(a.nm,b.nm) as nm, addr
 from a,b
 where a.nm=b.nm(+)
 union 
  select id, nvl(a.nm,b.nm) as nm, addr
  from a,b
  where a.nm(+)=b.nm

Open in new window

0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36493011
On mobile and cannot review the code.

I mentioned the 3113 is hard to track down.  Did you open the SR yet?  Until an Expert that can help you rewrite the SQL.
0
 
LVL 15

Assisted Solution

by:Devinder Singh Virdi
Devinder Singh Virdi earned 250 total points
ID: 36503353
Please look alert and trace files also if you are receiving any other errors at same time.
0
 

Author Comment

by:gs79
ID: 36511663
I dont have direct access to the file s.however I requested dba to take a look..he said there was a core dump and it s a bug which is not resolved until 11gr2..
I changed my sql to use left outrer join union with right outer join and the query worked fine..i think it does'nt like  full outer join syntax being called in a merge statement..i even reproduced the error with a simple example and the fix was again to remove "full outer join " 
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question