ORA-19011 in outer query but not in derive table-CodePudding

Here is the function I am using in the inner query and it executes against the whole data set without error with a group by.

RTRIM(XMLAGG(XMLELEMENT(E,LOCATION_CD,';').EXTRACT('//text()'))
    )AS PRAC_LOC

However when I bring it up into the outer query I get the error message 19011. Is there another coding strategy I could use to not get this error message? Do you think a CTE to isolate the query then join into the CTE would work? It's a log table so there are multiple entries so I guess I could isolate the join with the date = select max(date).

I was looking at all the other topics in regards to this error but nothing mentioned the error in just the outer query.

Here is a sample of the derived table

LEFT OUTER JOIN
(
    SELECT 
    PLACE_ID --Single Grouping on Place_ID
    , RTRIM(XMLAGG(XMLELEMENT(E,LOCATION_CD,';').EXTRACT('//text()'))) AS PRAC_LOC
    FROM GEOTABLE
    GROUP BY PLACE_ID
) GEO
ON GEO.Place_ID = P.Place_ID

CodePudding user response：

Adding the .getCLOBVal()) function to the end of the XML script allowed the script to run and use it in the outer query from my derive table.

RTRIM(XMLAGG(XMLELEMENT(E ,TLOCATION_CD || ',')).EXTRACT('//text()').getCLOBVal())