-
Please help-need SQL statement
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')
/************************************************** *************************************************
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|