Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-09-06
6
Medium Priority
?
1,648 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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 78

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 1000 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

877 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