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

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

Select all Open in new window

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

Select all Open in new window

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.

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 "