ORA-03113 & 3114 after select

From: Andreas Vandenberghe <hp275_at_skynet.be>
Date: Wed, 09 Mar 2005 07:57:46 +0100
Message-ID: <422e9e6b$0$10345$ba620e4c@news.skynet.be>

Hello,

I tried to include another join in this select (see the full outer join on a view), and i got an ORA-03113 error followed by a ORA-03114. so my session was killed.

before that the sql performed well.
did i just ask too much from Oracle ? (i'm not the dba)

  SELECT t.dim1 as account,

    a.rel_value AS master,
    r.description as mastername,
    b.rel_value AS accmgr,
    d.rel_value AS sector,
    c.rel_value AS segment,
    t.dim2 as agent,
    s.description as agentname,
    t.dim3 as client,
    t.period,

  f.country_code, f.zip_code,
    SUM(t.amount) AS amount
   , sum(nvl(u.numpol,0)) as numpol,
   sum(nvl(u.numassures,0)) as numass

   FROM (


        (

        (

         (
          (
           (
            (
             (
              (
               (aagirep2a t  INNER JOIN aglrelvalue a
		ON(1=1) AND t.dim2= a.att_value)
                INNER JOIN agldimvalue r
		ON (1=1) AND r.dim_value=a.rel_value)
              INNER JOIN aglrelvalue b
		ON(1=1) AND t.dim2 = b.att_value)
             INNER JOIN aglrelvalue z
		ON(1=1) AND b.rel_value = z.att_value)
            INNER JOIN acruserinfo z1
		ON(1=1) AND z.rel_value = z1.user_group)
           INNER JOIN aglrelvalue d
		ON(1=1) AND t.dim2 = d.att_value)
          INNER JOIN aglrelvalue c
		ON(1=1) AND d.rel_value = c.att_value)
        LEFT JOIN  agladdress f
		ON (1=1) AND f.dim_value = t.dim2 )
    INNER JOIN agldimvalue s
		ON (1=1) AND s.dim_value=t.dim2 )

   FULL OUTER JOIN
   ( select

     sum(numformuleagt) as numpol,  cie,
     sum(numassures) as numassures,
     to_number(trim(agent)) as agent,
     to_number(dtecrea) as period
     from uct_mt_numformagt
     where ((dtecrea between '200401' and '200401' ) OR
	(dtecrea between '200501' and '200502' ))
     and cie = 'E'
     Group by  to_number(trim(agent)), dtecrea, cie

   ) u
   ON u.cie = t.dim3 AND t.dim2 = u.agent AND t.period = u.period    )
   WHERE 1=1

   AND a.client = 'MG' AND a.rel_attr_id  = 'RC'
   AND a.attribute_id ='QF'
   AND r.client = 'MG' AND r.attribute_id = 'RC'
   AND b.client = 'MG' AND b.rel_attr_id  = 'RP'
   AND b.attribute_id =  'QF'
   AND z.client = 'MG' AND z.rel_attr_id  = 'AC'
   AND z.attribute_id = 'RP'

   AND z1.client = 'MG' AND z1.user_id = '<user_id>'

   AND c.client = 'MG' AND c.rel_attr_id  = 'RY'
   AND c.attribute_id = 'RX'
   AND d.client = 'MG' AND d.rel_attr_id  = 'RX'
   AND d.attribute_id = 'QF'
   AND t.dim3 = 'E'
   AND t.dim1 like ('700%')
   AND f.client = 'MG'
   AND f.address_type='1'
   AND f.attribute_id ='A4'
   AND t.client = 'MG'
   AND s.client='MG' and s.attribute_id = 'QF'

   GROUP BY a.rel_value, f.zip_code, t.dim2, t.dim1,

   b.rel_value, t.period, c.rel_value,
   d.rel_value, r.description, s.description,  t.dim3,
   t.client, f.country_code

   HAVING (SUM(t.amount) <> 0)

best regards,
andreas Received on Wed Mar 09 2005 - 00:57:46 CST