oracle - ORA-19011 : Character string buffer too small

Question

我编写了一个存储过程SP_DEMAND_QRY。如果 Demand 表中只有几个匹配行(5 或 6)，这会产生正确的值。但是，如果该表包含更多匹配行(> 6)，那么当我执行它时，我会收到此错误:

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;

我做错了什么？

Answer 1

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

您可以使用 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 进行了验证。