Recently while working on the client project, i can came across a situation where i have to insert the XML file as XMLType datatype in Oracle 10g table. I was trying to insert the XML file as CLOB object, but it was giving me some Exception. I tried below options for this
- Inserting the XML file as CLOB object.
- Inserting as String.
- Inserting as Object.
All these above options gave SQLException. When I Googled , I found that there is support for inserting the XML file as XMLType datatype. So if you guys are trying out inserting the XML file as CLOB/String then i would recommend to upgrade your JDBC driver to Oracle10g JDBC driver. You can download the Oracle10g JDBC driver from here.
I would like to share how we can insert the XML file in XMLType datatype column. Please is the tested code example for doing this .
[java]
/**********************************************************************************
* Created on Nov, 2010 Copyright(c) https://kodehelp.com All Rights Reserved.
**********************************************************************************
*/
package com.kodehelp.java.sample;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.jdbc.driver.OraclePreparedStatement;
/**
* This class contains the code logic for inserting the XML as XMLType in database.
*
* @author http://Vigilance.co.in
*
*/
public class InsertXMLType {
/**
* Constructor for this class
*
*/
public InsertXMLType() {
super();
}
/**
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception {
String JDBC_DRIVER = “oracle.jdbc.driver.OracleDriver”;
String JDBC_STRING = “jdbc:odbc:thin:@DBNAME:PORTNUMBER:SID”;
String USER_NAME = “USER_NAME”;
String PASSWD = “PASSWORD”;
Connection conn = null;
ResultSet rs = null;
Statement stmt = null;
OraclePreparedStatement pStmt = null;
try{
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(JDBC_STRING, USER_NAME, PASSWD);
stmt = conn.createStatement();
String insertQuery = “INSERT INTO TABLENAME (ID, NAME, STUDENTXML)”+
” VALUES(?,?,XMLTYPE(?))”;
pStmt = (OraclePreparedStatement) conn.prepareStatement(insertQuery);
pStmt.setObject(1, “001”);
pStmt.setObject(2, “VIGILANCE”);
pStmt.setStringForClob(3, “studentXMLString”);
pStmt.executeUpdate();
}catch(SQLException sqlEx){
if(pStmt!=null){
pStmt.close();
pStmt=null;
}
if(conn!=null){
conn.rollback();
conn.close();
conn = null;
}
throw new Exception(sqlEx);
}catch(Exception ex){
if(pStmt!=null){
pStmt.close();
pStmt=null;
}
if(conn!=null){
conn.rollback();
conn.close();
conn = null;
}
throw new Exception(ex.getMessage());
}finally{
if(pStmt!=null){
pStmt.close();
pStmt=null;
}
if(rs!=null){
rs.close();
rs=null;
}
if(stmt!=null){
stmt.close();
stmt=null;
}
if(conn!=null){
conn.close();
conn = null;
}
}
}
}
[/java]
This above java program will create the connection with database using JDBC driver and executes the prepared statement which inserts the XML file as XMLType column. Below are the changes you need to do in the above java program to run successfully.
- JDBC_STRING to correct URL to connect to your database.
- USER_NAME to your DB username.
- PASSWD to correct password of your database.
- Change the insert query.
I hope you enjoyed this article. Please keep visiting for new articles.