3 Replies Latest reply: May 9, 2012 8:19 AM by 936117 RSS

    XML concat - ORA-19011: Character string buffer too small

    248610
      SQL> desc send_file
      Name Null? Type
      ----------------------------------------- -------- ------------
      REQUEST NUMBER
      XML_DOC SYS.XMLTYPE


      insert into SEND_file(XML_DOC,REQUEST)
      SELECT
      XMLElement("report",
      XMLElement("id",
      XMLElement("request", REQUEST)
                ,XMLElement("project", 'A')
                ,XMLElement("reportingDate",to_char(sysdate,'YYYY-DD-MM HH24:MI:SS'))
                ,XMLElement("recordCount", count(REQUEST))
      ),
      XMLAgg(
      XMLElement("count",
      XMLElement("Name","Name")
      ,case when "cCode" is not null then
                     XMLElement("cCode","cCode") end
      ,XMLElement("lNumber","lNumber")
      ,XMLElement("Cases","Cases")
      )
      )
      -- )
      ),REQUEST
      FROM test_data_table group by request
      /


      this is working fine,
           but I want to add/concat '<?xml version="1.0" encoding="UTF-8" ?>' this text to the above query

      like select '<?xml version="1.0" encoding="UTF-8" ?>'||XMLElement("report",
      XMLElement("id",
      XMLElement("request", REQUEST).....rest of the query then

      I am getting this error
           ORA-19011: Character string buffer too small

      could you please let me know How do I cancat the value, as I said without that I have no issues even that's length is more than 4000, but now sure that the limit of XML type

      Thanks,