Thread: Inserting Large XML file into Oracle Database through java

Hello All,

I am trying to insert a large xml file into oracle database through java , i am getting the following error ,please help me to resolve d issue,itz very urgent.

ORA-30676: socket read or write failed
ORA-06512: at "SYS.XMLTYPE", line 5


Following is my code

import oracle.sql.CLOB;
import java.sql.Connection;
import java.sql.SQLException;
import java.io.Writer;

import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.DocumentBuilder;
import org.w3c.dom.Document;
import java.sql.SQLException;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.sql.*;
import oracle.jdbc.OraclePreparedStatement;
import oracle.sql.CLOB;
import oracle.xdb.XMLType;
public class InsertXml {
// private static Connection conn=null;
public static String getXMLString(java.sql.Connection conn, Document doc)throws SQLException

{

//String query="<?xml version=\"1.0\"?><customer><name>Joe Smith</name><Job>SoftWare</Job><title>Mathematician</title></customer>";
String SQLTEXT = "";
XMLType xml = null;
xml = XMLType.createXML(conn,doc);
//System.out.println("xml oject "+xml.toString());

try {
//xml = XMLType.createXML(conn,new FileInputStream("C://har1.xml"));
xml = XMLType.createXML(conn,doc);
// return xml.getStringVal().trim().replace("null", "").replace("\000", " ").replace("\0x0", " ").replace("\r\n", " ").replace("\n", " ");

} catch (Exception e) {
e.printStackTrace();
}
return xml.getStringVal();
//return xml.getStringVal().trim().replace("null", "").replace("\000", " ").replace("\0x0", " ").replace("\r\n", " ").replace("\n", " ");

}

private static void insertXML(String xmlData, Connection conn) {
CLOB clob = null;
String query;
//XMLType xml = null;
//xml = XMLType.createXML(conn,doc);
// Initialize statement Object
OraclePreparedStatement pstmt = null;
try{
//query="insert into xml_tab(id) values(?)";
//

query = "INSERT INTO xml_tab(xmldata) values(xmltype.createxml(?))";

// Get the statement Object
pstmt = (OraclePreparedStatement)conn.prepareStatement(que ry);
// xmlData is the string that contains the XML Data.
// Get the CLOB object.
clob = getCLOB(xmlData, conn);
System.out.println("clob is "+clob);
// Bind this CLOB with the prepared Statement
//pstmt.setInt(1,2);
// pstmt.setString(2,"test.xml");
pstmt.setObject(1,clob);
// pstmt.setObject(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 static CLOB getCLOB(String xmlData, Connection conn) throws SQLException{
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 arg[]) {

try {
Connection conn=null;
conn=DBConnection.getConnection();

File fXmlFile = new File("C:/har1.xml");
//String filetxt=fXmlFile.toString();
DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();
Document doc = dBuilder.parse(fXmlFile);
// doc.getDocumentElement().normalize();
String strxml=getXMLString(conn,doc);
insertXML(strxml,conn);

//doInsert(conn, doc);

} catch (Exception e) {
e.printStackTrace();
}
}




}

I'm no expert at databases, let alone oracle, but I think this statement is incorrect:

query = "INSERT INTO xml_tab(xmldata) values(xmltype.createxml(?))";

xmldata is one of your variables, but you are using it as the literal string "xmldata". Same kind of issue for the xmltype.createxml part. I believe this is causing a problem, but I'm not positive.

Hi KucerakJM,
Thnx for ur reply ,but my column name is xmldata ,it is not the string literal in the above insert query.

I see, well I don't think I will be much help here but I do see one thing that you may want to change. In the main method xml = XMLType.createXML(conn,doc); is called before the try/catch statement and then once more inside of it, just something a bit redundant. Wish I could be of more help.

i changed it to call once ,but still the same issue