Oracle CLOBs, XMLDB and JDBC

From: Brad (javawork_co_uk_at_yahoo.co.uk)
Date: 03/30/04 

Date: 30 Mar 2004 03:30:22 -0800

Hi all,

I'm trying to insert a row into my XMLDB table but I have the
following problem:

My xml is too big for varchar2 (>4k) so I have to use a CLOB in the
constructor for XMLType. However I can't create a new CLOB complete
with data in JDBC, so I have to create and empty CLOB and update it
later. Unfortunately this clashes with XMLDB which won't let me insert
a an empty document as it fails schema validation.

So, my workaround is to load the xml into another "staging" table and
then once the CLOB is populated, transfer it into the XMLType table.
Here's my stored procedure to do this:

create or replace procedure clobMove is
CURSOR c1 is
   select xml_content
   from xml_clobs
   where doc_id = 'clob1';
xmlClob CLOB;
BEGIN
open c1;
   fetch c1 into xmlClob;
   insert into bufr_xml values(XMLType.createXML(xmlClob));
   commit;
close c1;
END;

I can call this without any problems in SQL*Plus but the JDBC call
produces errors:

CallableStatement cs = conn.prepareCall("{call clobin}");
int rows = cs.executeUpdate();

SEVERE: Unable to load doc into database: ORA-22814: attribute or
element value is larger than specified in type

Any help would be very welcome.

Cheers,
Brad.

Relevant Pages

  • Re: Oracle CLOBs, XMLDB and JDBC
    ... Fixed now....the problem was that I had the schema location of the XML ... XMLDB expects it ... However I can't create a new CLOB complete ... > xmlClob CLOB; ...
    (comp.lang.java.databases)
  • Re: export to XML file - poor performance
    ... VARCHAR2) RETURN CLOB AS ... loading first wirh select into and using a loop for concatenation? ... I'm trying to create PL/SQL procedure which will export data to xml ... I loop from the data and create the output file concatenating the ...
    (comp.databases.oracle.misc)
  • Re: Crystal Report - From stored proc. to unbound fields
    ... First of all from the data returned in xml format (clob ... i genereated an xml schema file. ... I had to drag and drop the one i wanted on my report. ...
    (microsoft.public.dotnet.framework.adonet)
  • generate big XML pages with DBMS_XMLGEN and PL/SQL Web Toolkit
    ... DBMS_XMLGEN package for converting a query into XML and printing it on ... dones't work if the length of the clob is more than 32767 characters. ... Is there a way to print out a clob via PL/SQL Web Toolkit? ... « Se la pena è necessaria, non la si deve cancellare; ...
    (comp.databases.oracle.server)
  • Re: How is a CLOB stored when XML is created from query?
    ... write it to a file on the file system. ... If the table the query was run against has a column that is a clob its ... data is not returne in the final XML doc. ...
    (comp.databases.oracle.server)