-2

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
philipxy
  • 14,867
  • 6
  • 39
  • 83
VLOOKUP
  • 548
  • 4
  • 12
  • Debug questions require a [mre]--cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) – philipxy Oct 15 '22 at 02:20
  • 1
    ORA-19011: Character string buffer too small 19011. 00000 - "Character string buffer too small" *Cause: The string result asked for is too big to return back *Action: Get the result as a lob instead as mentioned previously and accurately this is executing fine in the inner script, but when bringing it into the outer query it's not executing. – VLOOKUP Oct 15 '22 at 14:52
  • Please clarify via edits, not comments. – philipxy Oct 15 '22 at 18:49
  • @philipxy I answered the question at the bottom, now it accounts for additional string length. It's been productionalized for several months now and it works perfectly. – VLOOKUP Jan 20 '23 at 13:43
  • I don`t know what the point of your comment is. My last comment is about your clarifying comment before it that should instead have been an edit to the question. My even earlier comment is about posting good questions on this site that is intended to be a repository of good Q&As. – philipxy Jan 20 '23 at 18:13

1 Answers1

0

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())
VLOOKUP
  • 548
  • 4
  • 12