nested clob xmlelemnt oracle sql select

196 views Asked by At

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
         ) ;
0

There are 0 answers