oracle ORA-19011: 字符串缓冲区太小
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26652879/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-19011: Character string buffer too small
提问by Avijit
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 Demandtable. But if that table contains more matching rows (>6) then I get this error when I execute it:
我写了一个存储过程SP_DEMAND_QRY。如果表中只有几个匹配的行（5 或 6），这会产生正确的值Demand。但是，如果该表包含更多匹配的行（> 6），那么我在执行它时会收到此错误：
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?
我究竟做错了什么？
回答by Alex Poole
The to_clob()function takes a character value, so you have an implicit conversion from the XMLTypereturned by XMLElement()into varchar2; once the length of the XML exceed 4k (since you're in an SQL context) you'll get that error.
该to_clob()函数采用一个字符值，因此您可以从XMLType返回的XMLElement()into进行隐式转换varchar2；一旦 XML 的长度超过 4k（因为您处于 SQL 上下文中），您将收到该错误。
You can use the XMLTypefunction getCLobVal()instead:
您可以改用该XMLType函数getCLobVal()：
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.
所以外部调用 toto_clob()已被删除，取而代之的是对 的调用XMLElement().getClobVal()。也使用大于 32k 的 XML 进行验证。
