package com.xmlTest.xmltest;

import java.io.File;
import java.io.StringWriter;
import java.io.Writer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.w3c.dom.Document;

import com.dbconn.connect.DBConnection;

import oracle.sql.CLOB;

public class Test {

private void insertXML(String xmlData, Connection conn) {
conn = DBConnection.getConnection();
CLOB clob = null;
String query;
String aId ="103";
PreparedStatement pstmt = null;
try{
query="update TABLE_NAME SET XML_COLUMN_TABLE= ? where ID ='"+aId+"'";

// Get the statement Object
pstmt = conn.prepareStatement(query);

// xmlData is the string that contains the XML Data.
// Get the CLOB object using the getCLOB method.
clob = getCLOB(xmlData, conn);
// Bind this CLOB with the prepared Statement
System.out.println("query is\t"+query);
//pstmt.setObject(1, clob);
pstmt.setClob(1, clob);
// Execute the Prepared Statement
if (pstmt.executeUpdate () == 1) {
System.out.println ("Successfully inserted a Purchase Order");
}
} catch(SQLException sqlexp){
sqlexp.printStackTrace();
} catch(Exception exp){
exp.printStackTrace();
}
}

private CLOB getCLOB(String xmlData, Connection conn) throws SQLException{
System.out.println("xmlData is\t"+xmlData);
CLOB tempClob = null;
try{
// If the temporary CLOB has not yet been created, create one
tempClob = CLOB.createTemporary(conn, true, CLOB.DURATION_SESSION);

// Open the temporary CLOB in readwrite mode, to enable writing
tempClob.open(CLOB.MODE_READWRITE);
// Get the output stream to write
Writer tempClobWriter = tempClob.getCharacterOutputStream();
// Write the data into the temporary CLOB
tempClobWriter.write(xmlData);

// Flush and close the stream
tempClobWriter.flush();
tempClobWriter.close();

// Close the temporary CLOB
tempClob.close();
} catch(SQLException sqlexp){
tempClob.freeTemporary();
sqlexp.printStackTrace();
} catch(Exception exp){
tempClob.freeTemporary();
exp.printStackTrace();
}
return tempClob;
}

public static void main(String[] args) {
Connection conn=null;
conn=DBConnection.getConnection();

try{
File fXmlFile = new File("C://Users///ac.xml");
//String filetxt=fXmlFile.toString();
DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();
Document doc = dBuilder.parse(fXmlFile);
// doc.getDocumentElement().normalize();

TransformerFactory transformerFactory = TransformerFactory.newInstance();
Transformer transformer = transformerFactory.newTransformer();
transformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");
DOMSource source = new DOMSource(doc);
StreamResult result = new StreamResult(new StringWriter());
transformer.transform(source, result);
String xmlStr = result.getWriter().toString();
xmlStr = xmlStr.replaceAll("[^\\u0009\\u000a\\u000d\\u0020-\\uD7FF\\uE000-\\uFFFD]", "");
//System.out.println("XML formed is\t"+xmlStr);
Test test = new Test();
test.insertXML(xmlStr, conn);


//doInsert(conn, doc);
}
catch (Exception e) {
e.printStackTrace();

}
}
}

I want to Insert XML with more than 4000 characters into a Oracle XMLTYPE column depending on some specific condition.

When I run my code I am getting an error as java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got CLOB

I am able to Insert the XML by manually going into the SQL Developer and then opening the editor. I want it to be Inserted through by Java Program.

Bind a CLOB instance or a string to an INSERT, UPDATE, or DELETE statement, and use the XMLType constructor inside SQL to construct the XML instance. Example 13-5 illustrates this.

Use setObject() in the PreparedStatement to set the entire XMLType instance. Example 13-6 illustrates this.