So let’s say you are trying to use a function to return a CLOB/NCLOB but you encounter the error:
ORA-19011: Character string buffer too small.
Basically this comes down to the limit of 4000 characters in the intermediate buffer prior to loading into the return value. For example consider the following scenario:
Here is statement:
SELECT a.PRIMARYID,
CATALOGUE.GetCatalogue( a.PRIMARYID) AS CATALOGUE,
FROM TBL_CATALOGUES a
Uses Function:
FUNCTION GetCatalogue(catlogue_id IN NUMBER)
RETURN NCLOB AS catlogueXml NCLOB;
BEGIN
select TO_CLOB(
xmlelement(“Catalague”,
XMLAGG (XMLELEMENT (“Course”,
xmlattributes (J.COURSEID, J.ENTRYDATE, J.TITLE, J.ENTRY AS ENTRY
)
)
)) INTO catlogueXml
FROM TBL_COURSES J
WHERE
J.CATALOGUEID = catlogue_id;
RETURN(catlogueXml);
END;
One work around is to put this into a view to avoid having to put results into an intermediate buffer.
CREATE OR REPLACE VIEW CATALOGUECLOBVIEW
(PRIMARYID, XMLCOLUMN)
AS
SELECT PRIMARYID,
(XMLELEMENT (“Catalogue”,
XMLAGG (XMLELEMENT (“Course”,
xmlattributes (J.COURSEID, J.ENTRYDATE, J.TITLE, J.ENTRY AS ENTRY
)
)
)).getClobVal() AS XMLCOLUMN
FROM TBL_COURSES J
GROUP BY PRIMARYID
Now you can just to a join against this view and still get your XML out. Sweet!!!