Solved

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

Posted on 2011-09-06
6
1,519 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
  • 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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now