Thursday, February 9, 2012

Work around to insert XML to database Oracle OCI & Hibernate

The workaround is using a PL/SQL embedded in Java

String XmlData = ((String)cr.get("xml")).replace('\'', '`');
StringBuffer xmlSP = new StringBuffer();
xmlSP/*.append("DECLARE ")
.append("\n")
.append(" l_xml CLOB := ?;")
.append("\n")*/
.append(" BEGIN \n")
.append(" INSERT INTO xml_data")
.append("\n")
.append(" (id, xml)")
.append("\n")
.append(" VALUES ")
.append("\n")
.append(" (my_seq.nextval, ?);")
.append("\n")
.append("END;");

logger.info(xmlSP.toString());
Connection con = session.connection();
try {
CallableStatement stmt = con.prepareCall(xmlSP.toString());
Connection poolConn = stmt.getConnection();
//Convert the poolConnection to OracleConnection.
OracleConnection oracleConn= (OracleConnection) ((DelegatingConnection) poolConn).getInnermostDelegate();
//Convert the input request string to XMLTYPE object.
oracle.xdb.XMLType requestXMLTYPE = oracle.xdb.XMLType.createXML(oracleConn, XmlData);
stmt.setObject(1, requestXMLTYPE);
boolean retCode = stmt.execute();
logger.info("Inserted data into bmc_cm_original_req");
} catch (SQLException e) {
logger.error("Sql Exception while inserting into bmc_cm_original_req " + e.getMessage());
} catch (Exception e) {
logger.error("Sql Exception while inserting into bmc_cm_original_req " + e.getMessage());
}

No comments:

Post a Comment