Home » SQL & PL/SQL » SQL & PL/SQL » ORA 22835: buffer too small for blob to char
ORA 22835: buffer too small for blob to char [message #250561] Tue, 10 July 2007 07:58 Go to next message
Elenar
Messages: 3
Registered: July 2007
Junior Member
Hello,

I have this problem with the message cited above. I know i must use in some way DBMS_LOB.SUBSTR to correct this matter. But I didn't understand how...(and still don't)

Let me explain what my function do. The main idea is to generate a xml file by taking different datas from 3 tables to have the informations in xml format for exportation.

I have a function exportmodel who load the model in the xml, then call a function (exportviews) who adds the views in the xml and at the end a function who adds all the commands (exportcommands)of each view inthe xml.

Structure:
<XML><MODEL>...Model1.xml<VIEW>....View1.xml<COMMAND>...Command1.xml></COMMAND><COMMAND>...Commandn.xml>/COMMAND></VIEW><VIEW >...Viewn...</VIEW>MODEL>
model
/ \
Viewx Viewy
/ \ / \
cmdx1 cmdx2 cmdy1 cmdy2


(The .xml represents the sub-tree holding all the acquired informations)

So at the end the error comes with 20000 instead of the max which is 4000. on the return of the exportmodel.

How can I avoid this max data limit?

The two functions exportviews and exportcommand are called for each view and command.

My functions are in plsql and must return an xmlfile for my delphi application.

I would be grateful if someone could help me solve this problem.
Re: ORA 22835: buffer too small for blob to char [message #250666 is a reply to message #250561] Tue, 10 July 2007 14:39 Go to previous messageGo to next message
toyway
Messages: 32
Registered: July 2002
Member
Define CLOB variable in your package and use them to construct your output. To manipulate with CLOB you can use package DBMS_LOB.
If you need to convert CLOB to XML you can use xmltype.createxml
Next URL points to Oracle documentation " Generating XML Data from the Database" which might be helpful in your case:
http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96620/xdb12gen.htm#1656

HTH
Oleg

[Updated on: Tue, 10 July 2007 14:40]

Report message to a moderator

Re: ORA 22835: buffer too small for blob to char [message #250768 is a reply to message #250666] Wed, 11 July 2007 04:31 Go to previous messageGo to next message
Elenar
Messages: 3
Registered: July 2007
Junior Member
Thx for the answer. But either I haven't understood your answer, or it doesn't fit my problem.Besides, I have improved my code to have only one big sql request to generate the xml. Here is the body of the functions:

  function Get_Sea_Xml(
    aIssueId     in ISSUES._ISSUES_ID%type
  )
    return clob
  is
    SeaXml xmltype;
  begin
    SeaXml  := PACKAGE.Get_Sea_XmlType(aIssueId);
    return UTILS.XmlEncodingDef || SeaXml.getClobVal();
  end;

  function Get_Sea_XmlType(
    aIssueId     in ISSUES.ISSUES_ID%type
  )
    return xmltype
  is
    SeaXml xmltype;

  begin
   select XMLElement
          (TABLES_INFORMATIONS
          , XMLElement(type, SeaType)
          , XMLElement(SITE, 'Site')
            , XMLAGG(XMLElement(MODELES
             , XMLELEMENT(NAME, SEM.descr)
             , XMLElement(VIEW     
               , XMLElement(NAME, VUE.descr)
               , XMLElement(SEV_XML, xmlcdata (VUE.sev_xml) ) 
               , XMLELEMENT(COMMAND
                  , XMLElement(NAME, CMD.descr)
                  , XMLElement(SEC_XML, xmlcdata (CMD.SEC_XML) )
               )                           
             )
           ))
          )

    into SeaXml
    from ...
    where ...
    
    return SeaXml;
  end;;


For the header:

  function Get_Sea_Xml(
    aIssueId     in ISSUES.ISSUES_ID%type
  )
    return clob;

  function Get_Sea_XmlType(
    aIssueId     in ISSUES.ISSUES_ID%type
  )
    return xmltype


So it builds the xml by composing from 3 tables where 2 columns already have xml data ( it is why i use xmlcdata)

The problem is that in some cases the xml is too big and generates the error. When the xml is small enough ,the xml is created correctly.
Re: ORA 22835: buffer too small for blob to char [message #251021 is a reply to message #250768] Thu, 12 July 2007 03:35 Go to previous message
Elenar
Messages: 3
Registered: July 2007
Junior Member
Ok i solved my problem. The problem was in the Cdata type who can't take more than 4000. And some of the xml in the table were too big.

So to avoid the cdata problem i used a function who encoded the xml of these tables, so i could have them in my xml and not being treated as xml.
Previous Topic: PL/SQL - DATETIME variable
Next Topic: viewing all create and alter statements
Goto Forum:
  


Current Time: Sat Dec 03 04:08:01 CST 2016

Total time taken to generate the page: 0.25154 seconds