Avatar of gs79
gs79

asked on 

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

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

Oracle Database

Avatar of undefined
Last Comment
gs79
Avatar of gs79
gs79

ASKER

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

ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of gs79
gs79

ASKER

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

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.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of gs79
gs79

ASKER

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 " 
Oracle Database
Oracle Database

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo