Problem whit load of xml file into Oracle using StringBuffer

[DragonL]

I have a problem whit load of xml file into Oracle using StringBuffer.
I get a java.sql.SQLException error “setString can only process strings of less than 32766 characters”.
My problem a cure when I try to bind the CLOB with the prepared Statement in the StringBuffer. There are no problem when I use a small xml file for test, but when it contain +55 records I get the error.
But I need the program to handle xml files containing anything from 1 to 10.000 records.

Anny one whit and idea of how I can get the contents of the StringBuffer into the database object, get around the size problem of the toString function?

Java code:

import java.io.BufferedReader;
import java.io.IOException;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import oracle.sql.CLOB;

import javax.xml.transform.stream.StreamSource;
import javax.xml.validation.Schema;
import javax.xml.validation.SchemaFactory;
import javax.xml.validation.Validator;


public class insertXML_test{

    /**
     *  This method inserts a large XML document into Table “POTABLE_MBN " 
     *  where "purchaseOrder" is an XMLType column.
     * */

    private static void insertXML(File xmlData, Connection conn) {
      CLOB clob = null;
      String query;

      // Initialize statement Object
      PreparedStatement pstmt = null;
        
      try{
        query = "INSERT INTO POTABLE_MBN (purchaseOrder) VALUES (XMLType(?)) ";
        // Get the statement Object
        pstmt = conn.prepareStatement(query);
        System.out.println("insert : " + query);

        //Get the jvm heap size.
        long heapSize = Runtime.getRuntime().totalMemory();
        //Print the jvm heap size.
        System.out.println("Heap Size = " + heapSize);
        
        // xmlData is the string that contains the XML Data.
        StringBuffer buf = new StringBuffer();
        
        try{
            BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(xmlData)));
            String tmp;
            tmp = br.readLine();

            int x = 1;
            while (tmp != null)
            {
                buf.append(tmp + "\n");
                //System.out.println("Test : line id > " + x + "  text > " + tmp + "\n");
                tmp = br.readLine();
                x = x + 1;
            }
            System.out.println("Test : line id > " + x + "  text > " + tmp + "\n");
            br.close();
            }
            catch(Exception ex)
            {
            ex.printStackTrace();
        }
        
        // Bind this CLOB with the prepared Statement
        pstmt.setObject(1, buf.toString()); 

        // Execute the Prepared Statement
        if (pstmt.executeUpdate () == 1) {
        System.out.println ("Successfully inserted xml file in DB!");
        conn.commit();
        conn.close();
        }
      } catch(SQLException sqlexp){
            sqlexp.printStackTrace();
      } catch(Exception exp){
            exp.printStackTrace();
      }
    }

    private static int validate_xml() { 
        try{
            String schemaLang = "http://www.w3.org/2001/XMLSchema";
            SchemaFactory factory = SchemaFactory.newInstance(schemaLang);
            //Schema schema = factory.newSchema(new StreamSource("C:\\Work\\sus\\xsd\\SU_InstanceMasterDataInformationStructure.xsd"));
            Schema schema = factory.newSchema(new StreamSource("C:\\Work\\sus\\xsd\\SU_ReportMasterDataInformationStructure.xsd"));
            Validator validator = schema.newValidator();
            validator.validate(new StreamSource("C:\\Work\\sus\\InstanceMasterDataInformationStructure.xml"));
            System.out.println("Validation ok");
            return 1;
        } catch(Exception exp){
            exp.printStackTrace();
            System.out.println("Validation Error");
            return 0;
        }
    }

    public static void main(String[] args) throws IOException,
        ClassNotFoundException, SQLException  {
            System.out.println("Start load and validation of xml file to DB");
            int ValResault = validate_xml(); 
            if(ValResault == 1){
                // Load the driver
                Class.forName ("oracle.jdbc.driver.OracleDriver");
                // Connects to the database
                Connection con = DriverManager.getConnection("jdbc:oracle:thin:sus/[e-mail]:1522:SASDEV");
                System.out.println("Got Connection " + con);
                try {
                    File file = new File("C:\\Work\\sus\\InstanceMasterDataInformationStructure.xml");
                    insertXML(file, con);
                   
                } catch (Exception e) {
                      e.printStackTrace();
                }
            System.out.println("Load and validation of xml file finish");
            }
        }
}

Log output:
Start load and validation of xml file to DB
Validation ok
Got Connection oracle.jdbc.driver.T4CConnection@2acc65
insert : INSERT INTO POTABLE_MBN (purchaseOrder) VALUES (XMLType(?))
Heap Size = 2031616
Test : line id > 1250 text > null

java.sql.SQLException: setString can only process strings of less than 32766 characters
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:138)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:175)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:240)
at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:4772)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:7620)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:7572)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8183)
at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:8166)
at dk.cbs.sus2.insertXML_test.insertXML(insertXML_test.java:91)
at dk.cbs.sus2.insertXML_test.main(insertXML_test.java:135)
Process exited with exit code 0.

Any help on this one is greatly a priciest.

[masijade]

Use the setCharacterStream method of PreparedStatement and feed a Reader directly to the PreparedStatement.