Ora-19011: Character String Buffer Too Small
I Have Written a Stored Procedure Sp_Demand_Qry. This Produces the Correct Value If There Are Only a Few Matching Rows (5 or 6) in the Demand Table. but If...
I have written a stored procedure SP_DEMAND_QRY. This produces the correct value if there are only a few matching rows (5 or 6) in the Demand table. But if that table contains more matching rows (>6) then I get this error when I execute it:
Error:
-------
ORA-19011: Character string buffer too small
ORA-06512: at line 7
The procedure is:
create or replace PROCEDURE SP_DEMAND_QRY
(
USR IN VARCHAR2
, OUT_CLOB OUT CLOB
) AS
BEGIN
SELECT to_clob(XMLElement("DEMANDS",XMLAgg(XMLElement("Demand"
,XMLElement("DemandId",dmnd_id)
,XMLElement("CreatedBy",CREATED_BY)
,XMLElement("CreatedDate",CREATED_DATE)
,XMLElement("Designation",DESIGNATION)
,XMLElement("Experience",EXPERIENCE)
,XMLElement("PrimarySkill",PRIMARY_SKILL)
,XMLElement("SecondarySkill",SECONDARY_SKILL)
,XMLElement("OtherSkill",OTHER_SKILL)
,XMLElement("RequiredDate",REQUIRED_DATE)
,XMLElement("ProbablePercentage",PROBABLE_PERCENTAGE)
,XMLElement("CriticalFlag",CRITICAL_FLG)
,XMLElement("AssignedFlag",ASSIGNED_FLG)
,XMLElement("AccountName",ACCOUNT_NAME)
,XMLElement("OpportunityName",OPTY_NAME)
,XMLElement("AccountPOC",ACCNT_POC)
,XMLElement("COE",COE)
,XMLElement("DemandType",DEMAND_TYPE)
,XMLElement("Location",LOC)
,XMLElement("ExpectedRole",EXPECTED_ROLE)
,XMLElement("ConfidenceFactor",CONFIDENCE_FACTOR)
,XMLElement("EndDate",END_DT)
,XMLElement("HiringSO",HIRING_SO)
,XMLElement("HiringSOId",HIRING_SO_ID)
,XMLElement("Comments",COMMENTS)
))))
into OUT_CLOB
from demand s
where s.CREATED_BY=usr;
--DBMS_output.put_line(OUT_CLOB);
END SP_DEMAND_QRY;
What am I doing wrong?
1 Answer
The to_clob() function takes a character value, so you have an implicit conversion from the XMLType returned by XMLElement() into varchar2; once the length of the XML exceed 4k (since you're in an SQL context) you'll get that error.
You can use the XMLType function getCLobVal() instead:
SELECT XMLElement("DEMANDS",
XMLAgg(XMLElement("Demand"
,XMLElement( "DemandId",dmnd_id)
,XMLElement( "CreatedBy",CREATED_BY)
...
,XMLElement("Comments",COMMENTS)
))).getClobVal()
into OUT_CLOB
...
So the outer call to to_clob() has been removed, and replaced with a call to XMLElement().getClobVal().
Verified with XML greater than 32k as well.
Robert Thorne covers electric vehicle innovations, autonomous driving systems, global mobility trends, and automotive engineering developments.
