Hi all,

I am running this below select and it gives ORA-22813 error.
Ora-22813:- The Collection value from one of the inner sub queries has exceeded the system limits and hence this error.

The Select has many inner Select statements and outerjoins which return a huge amount data into many collections.
Could you all please help me modifying/changing this query process in any other way.for example-i feel using collections is a factor.

***I am sorry for putting such a long select...but i need desperate help.Need SQL statment possible to replace collections.

/*****************************************BEGIN
SELECT * FROM (SELECT

mco.ID,

mco.NAME ,

mco.DESC,

mco.TYPE,

mco.METHOD,

XMLElement("PLAN",XMLAttributes( TP.PLN_ID as "id" ),

XMLELEMENT("QUANTITY", TP.QUANTITY),

XMLELEMENT("UNIT", TP.UNIT),

XMLElement("TOTAL_NO", TP.numcredits ),

XMLELEMENT("CREDIT_BASED", TP.CREDIT_BASED),

XMLELEMENT("FOR_CERT", TP.FOR_CERT),

XMLELEMENT("COMPANY_ORG_NAME", TP.CERT_ORG_NAME),

XMLELEMENT("Company_ORG_ID", TP.CERT_org_id ),

XMLElement("OBJ_LIST", TP.OBJ_LIST )

).extract('/').getClobVal() AS PLAN_LIST



FROM

emp_master_catalog mco

INNER JOIN

(SELECT cl_tp.TPLAN_ID,



obj_list.OBJ_LIST

FROM

emp_TR_PLAN cl_tp

LEFT OUTER JOIN

emp_accrediting_org cl_oa on cl_tp.TP_XML_DATA.extract('//creditingOrg/text()').getStringVal() = cl_oa.CRED_ORG

INNER JOIN

(SELECT *

FROM

(SELECT

cl_tpo.PLAN_ID AS OBJ_PLAN_ID,

XMLAgg**(

XMLElement("OBJ",

XMLElement("OBJ_ID",cl_tpo.T_OBJ_ID ),

XMLElement("OBJ_NAME",cl_to.T_OBJ_NAME ),

XMLElement("ACT_LIST", act_list.ACT_LIST )

)

) as OBJ_LIST

FROM

TP_OBJ cl_to

INNER JOIN

TP_TRn_OBJ cl_trn ON cl_trn.T_OBJ_ID = cl_to.T_OBJ_ID

INNER JOIN

(SELECT *

FROM

(SELECT cl_ta.T_OBJ_ID AS ACTIv_TOBJ_ID, XMLAgg(

XMLElement("ACTIVITIES",

XMLElement("ACTIV_ID",cl_ta.ACTIV_ID ),

XMLElement("CAT_ID",COALESCE(cl_c.CAT_ID, COALESCE( cl_e.CAT_ID, cl_t.LOG_ID ) ) ),


)) as act_list

FROM EMP_TRAIN_OBJ_ACTIV clm_ta

LEFT OUTER JOIN

(

SELECT distinct lcc.LEARN_ID, lcc.CREDIT

FROM emp_TR_PLAN tp

INNER JOIN emp_TR_PLAN_OBJECTIVE tpo on tp.P_ID = tpo.T_ID

INNER JOIN EMP_TRAIN_OBJ_ACTIV toa on tpo.T_OBJ_ID = toa.T_OBJ_ID

INNER JOIN emp_LEARN_CREDIT elc on elc.LEARNING_ID = toa.LEARNING_ID
and tp.TP_XML_DATA.extract('//creditingOrg/text()').getStringVal() = elc.ACC_ID

where tp.p_id ='36318851805687'

) cl_lac ON cl_lac.LEARN_ID = clm_toa.ACTIVITY_ID <----CREATING CL_LAC

LEFT OUTER JOIN

( SELECT ch.

FROM

LEFT OUTER JOIN

LEFT OUTER JOIN

) XX_c ON XX_c.XX_ID = clm_toa.ACTIVITY_ID <----CREATING SIMILIAR

LEFT OUTER JOIN

(SELECT
FROM table
LEFT OUTER JOIN table on ) xx_e ON x_e.EVENT_ID = xx_c.ACTIVITY_ID

LEFT OUTER JOIN

(SELECT
FROM table
LEFT OUTER JOIN table on table.test_ = ) yy_t ON yy_id = cl_to._ID

GROUP BY ) ) activ_list ON activ_list.___= cl_tp._

GROUP BY ) ) obj_list ON obj_list.OB__ID = cl_tp._ID

)TP ON TP.LAN_ID = mco.CATA_ID

WHERE

mco._ITEM_ID = '21711363' and mco.com_ID = '12')

/************************************************** *************************************************