ORA-19011:字符串缓冲区太小 [英] ORA-19011: Character string buffer too small

问题描述

我已经写了一个存储过程SP_DEMAND_QRY.如果Demand表中只有几行匹配的行(5或6)，这将产生正确的值.但是，如果该表包含更多匹配行(> 6)，则在执行它时会出现此错误:

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

过程是:

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;

我在做什么错了?

推荐答案

to_clob()函数采用字符值，因此您可以将XMLElement()返回的XMLType隐式转换为varchar2；一旦XML的长度超过4k(因为您处于SQL上下文中)，就会收到该错误.

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.

您可以改为使用XMLType函数getCLobVal():

    SELECT XMLElement("DEMANDS",
                XMLAgg(XMLElement("Demand"
                            ,XMLElement( "DemandId",dmnd_id)
                        ,XMLElement( "CreatedBy",CREATED_BY)
...
    ,XMLElement("Comments",COMMENTS)
                       ))).getClobVal()
    into OUT_CLOB
    ...

因此，已删除对to_clob()的外部调用，并替换为对XMLElement().getClobVal()的调用. 还使用大于32k的XML进行了验证.

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.

这篇关于ORA-19011:字符串缓冲区太小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！