I get this error when trying to run this sql scrip, I've searched alot for converting clob to varchar2 but useless
ORA-00932: inconsistent datatypes: expected - got CLOB
SELECT XMLELEMENT("ejada:PrivilegeResourcesList",XMLATTRIBUTES(NOENTITYESCAPING GET_MCR_PARAMETER('xmlns:core') AS "xmlns:core", GET_MCR_PARAMETER('xmlns:ejada') AS "xmlns:ejada"),
XMLAGG(XMLELEMENT("ejada:PrivilegeResourcesInfo",XMLELEMENT("ejada:PrivilegeRec",
XMLELEMENT("ejada:FuncId","F_CODE"),
XMLELEMENT("ejada:SCId","PS_CHANNEL_ID"),
XMLELEMENT("ejada:SrcResourcesList","SRCLIST"),
XMLELEMENT("ejada:TargResourcesList","TRGLIST"),
XMLELEMENT("ejada:Status","PS_STATUS")
)))) .GETCLOBVAL()
INTO P_PRIVILEGE_RESOURCES_LIST
from (
SELECT distinct
F_CODE, PS_CHANNEL_ID,
(
SELECT XMLAGG(XMLELEMENT ("core:ResourceInfo",XMLELEMENT ("core:ResourceId",
DECODE(PR_RESOURCE_TYPE_ID,
'1', XMLELEMENT("core:AcctId",XMLELEMENT("core:AcctId","PR_RESOURCE_VALUE")),
'2', XMLELEMENT("core:BillId",XMLELEMENT("core:BillNum","PR_RESOURCE_VALUE")),
'3', XMLELEMENT("core:BenId",XMLELEMENT("core:BenCode","PR_RESOURCE_VALUE"))
))
,XMLELEMENT("core:ResourceType","PRT_RESOUCE_TYPE_NAME_E")
)).GETCLOBVAL() FROM(SELECT PR_RESOURCE_TYPE_ID, PR_RESOURCE_VALUE, PRT_RESOUCE_TYPE_NAME_E FROM PERMISSION_RESOURCES, PERM_RESOURCE_TYPES WHERE PR_PERMISSION_ID = P.PS_PERMISSION_ID AND PR_USAGE_TYPE = 'S' AND PR_RESOURCE_TYPE_ID = PRT_RESOURCE_TYPE_ID)
) SRCLIST,
(
SELECT XMLAGG(XMLELEMENT ("core:ResourceInfo",XMLELEMENT ("core:ResourceId",
DECODE(PR_RESOURCE_TYPE_ID,
'1', XMLELEMENT("core:AcctId",XMLELEMENT("core:AcctId","PR_RESOURCE_VALUE")),
'2', XMLELEMENT("core:BillId",XMLELEMENT("core:BillNum","PR_RESOURCE_VALUE")),
'3', XMLELEMENT("core:BenId",XMLELEMENT("core:BenCode","PR_RESOURCE_VALUE"))
))
,XMLELEMENT("core:ResourceType","PRT_RESOUCE_TYPE_NAME_E")
)).GETCLOBVAL() FROM(SELECT PR_RESOURCE_TYPE_ID, PR_RESOURCE_VALUE, PRT_RESOUCE_TYPE_NAME_E FROM PERMISSION_RESOURCES, PERM_RESOURCE_TYPES WHERE PR_PERMISSION_ID = P.PS_PERMISSION_ID AND PR_USAGE_TYPE = 'D' AND PR_RESOURCE_TYPE_ID = PRT_RESOURCE_TYPE_ID)
) TRGLIST,
PS_STATUS
FROM FUNCTIONS F , SERVICES S , PERMISSIONS P
WHERE PS_STATUS = 'A'
--AND PS_COMP_TYPE_ID = v_component_type
-- AND Ps_COMPONENT_ID = v_component_id
AND PS_ROLE_ID = P_ROLE_ID
AND PS_ROLE_ID IN (SELECT R_ROLE_ID
FROM ROLES
WHERE R_ROLE_ID=P_ROLE_ID AND
R_COMP_TYPE_ID = 'ORGZ' AND
R_COMPONENT_ID= P_ORG_ID)
AND PS_FUNCTION_ID = F_code
AND F_SERVICE_ID = S_SERVICE_ID
-- AND PS_PERMISSION_ID = PR_PERMISSION_ID
ORDER BY F_CODE
) ;