Inserting Large XML file into Oracle Database through java,Please help

[sv hari]

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(xmlvalue) values(xmltype(?))";

            // 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();
        }
    }
} 

[Rob Spoor]

Welcome to the Ranch!

hari java wrote:please help me to resolve d issue,itz very urgent.

Please UseRealWords ("the", "it's" instead of "d", "itz"), and EaseUp. There is no such thing as urgent around here. Also, please UseCodeTags next time. Don't forget to maintain proper indentation. I've added both this time.

Which part of this code is throwing the exception? getXMLString, insertXML, or getCLOB? More importantly, on which line does this exception occur?

[sv hari]

Hi Rob,
Thanks for your reply and for guidance, i am new to this forums, and regarding my post , i am getting the error in the insertXML method.

[Wendy L Gibbons]

Have you tried searching on the oracle error numbers, oracles numbers are usually quite precise, and there are often really helpfull pages out there already.

[Martin Vashko]

Unfortunately, in this case the error code does not say much. It seems that the process at the database side crashed (thus the socket error). Some additional info might be in the database logs.

sv hari, are you using the latest Oracle JDBC driver (ojdbc5.jar / ojdbc6.jar)? If not, I'd suggest trying to switch to these drivers first.

[sv hari]

Hi Martin and Windy ,
Thanks for your suggestios , the issue is resolved with some code changes, and i used the ojdbc14.jar

[Wendy L Gibbons]

Could you let us know what those code changes are, to help other ranchers, and other people who stumble on this thread searching for help.

[sv hari]

Hello all ,
The below code worked resolved my issue.

import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.DocumentBuilder;

//import org.apache.openjpa.jdbc.kernel.exps.GetColumn;
import org.w3c.dom.Document;
import java.sql.SQLException;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.Writer;
import java.sql.*;
import oracle.jdbc.OraclePreparedStatement;
import oracle.xdb.XMLType;
import oracle.sql.CLOB;

public class InsertXml {
	// private static Connection conn=null;

	public static void doInsert(java.sql.Connection conn, CLOB doc)

	{

		try {
			CLOB clob = null;

			String SQLTEXT = "INSERT INTO xml_tab(CLOBDATA) VALUES(?)";

			OraclePreparedStatement sqlStatement = null;
			sqlStatement = (OraclePreparedStatement) conn
					.prepareStatement(SQLTEXT);

			sqlStatement.setObject(1, doc);

			if (sqlStatement.executeUpdate() == 1) {
				System.out.println("Records inserted successfully");
			}
		} catch (SQLException sqlexp) {
			sqlexp.printStackTrace();
		} catch (Exception exp) {
			exp.printStackTrace();
		}
	}

	public static CLOB getXMLAsClob(java.sql.Connection conn, Document doc)
			throws SQLException

	{

		// String SQLTEXT = "";
		XMLType xml = null;
		xml = XMLType.createXML(conn, doc);
		return xml.getClobVal();

	}

	public static void main(String arg[]) {

		try {
			Connection conn = null;
			conn = DBConnection.getConnection();
			File fXmlFile = new File("C:/hari.xml");
			FileInputStream fi = new FileInputStream(fXmlFile);
			BufferedInputStream bi = new BufferedInputStream(fi);

			DocumentBuilderFactory dbFactory = DocumentBuilderFactory
					.newInstance();
			DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();

			Document doc = dBuilder.parse(bi);

			CLOB xmlclob = getXMLAsClob(conn, doc);
			doInsert(conn, xmlclob);

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

}