Converting clob to string:
Clob clob=rs.getClob(2);
String str=(String)clob.getSubString(1,(int)clob.length());
System.out.println("Clob Data is : "+str);
passing the xml content as string.
table1
ID int
XML CLOB
import oracle.jdbc.OraclePreparedStatement;
/*
Your Code
*/
void insert(int id, String xml){
try {
String sql = "INSERT INTO table1(ID,XML) VALUES ("
+ id
+ "', ? )";
PreparedStatement ps = conn.prepareStatement(sql);
((OraclePreparedStatement) ps).setStringForClob(1, xml);
ps.execute();
result = true;
} catch (Exception e) {
e.printStackTrace();
}
}
I had similar issue. Changed one of my table column from varchar2 to CLOB. I didn't needed to change any java code. I kept it as setString(..) only so no need to change set method as setClob() etch if you are using following versions ATLEAST of Oracle and jdbc driver.
I tried in In Oracle 11g and driver ojdbc6-11.2.0.4.jar
This code worked for me. I use ojdbc6-11.2.0.2.jar.
java.sql.Connection con;
javax.xml.bind.Marshaller marshaller;
Clob xmlClob = con.createClob();
try {
try (Writer xmlClobWriter = xmlClob.setCharacterStream(1)) {
m.marshal(jaxbObject, xmlClobWriter);
} // xmlClobWriter.close();
try (PreparedStatement stmt = con.prepareStatement("INSERT INTO table (xml) values(?)")) {
stmt.setClob(1, xmlClob);
stmt.executeUpdate();
}
} finally {
xmlClob.free();
}
Try this , there is no need to set its a CLOB
public static void main(String[] args)
{
try{
System.out.println("Opening db");
Class.forName("oracle.jdbc.driver.OracleDriver");
if(con==null)
con=DriverManager.getConnection("jdbc:oracle:thin:@192.9.200.103:1521: orcl","sas","sas");
if(stmt==null)
stmt=con.createStatement();
int res=9;
String usersSql = "{call Esme_Insertsmscdata(?,?,?,?,?)}";
CallableStatement stmt = con.prepareCall(usersSql);
// THIS THE CLOB DATA
stmt.setString(1,"SS¶5268771¶00058711¶04192018¶SS¶5268771¶00058712¶04192018¶SS¶5268772¶00058713¶04192018¶SS¶5268772¶00058714¶04192018¶SS¶5268773¶00058715¶04192018¶SS¶5268773¶00058716¶04192018¶SS¶5268774¶00058717¶04192018¶SS¶5268774¶00058718¶04192018¶SS¶5268775¶00058719¶04192018¶SS¶5268775¶00058720¶04192018¶");
stmt.setString(2, "bcvbcvb");
stmt.setString(3, String.valueOf("4522"));
stmt.setString(4, "42.25.632.25");
stmt.registerOutParameter(5,OracleTypes.NUMBER);
stmt.execute();
res=stmt.getInt(5);
stmt.close();
System.out.println(res);
}
catch(Exception e)
{
try
{
con.close();
} catch (SQLException e1) {
}
}
}
}
Take a look at the LobBasicSample for an example to use CLOB, BLOB, NLOB datatypes.
For this purpose you need to make the connection result set
ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE
Connection con=null;
//initialize connection variable to connect to your database...
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
String query="Select MYCLOB from TABLE_NAME for update";
con.setAutoCommit(false);
ResultSet resultset=stmt.executeQuery(query);
if(resultset.next()){
oracle.sql.CLOB clobnew = ((OracleResultSet) rss).getCLOB("MYCLOB");
PrintWriter pw = new PrintWriter(clobnew.getCharacterOutputStream() );
BufferedReader br = new BufferedReader( new FileReader( new File("filename.xml") ) );
String lineIn = null;
while( ( lineIn = br.readLine() ) != null )
pw.println( lineIn );
pw.close();
br.close();
}
con.setAutoCommit(true);
con.commit();
}
Note: its important that you add the phrase for update at the end of the query that is written to select the row...
Follow the above code to insert the XML file
You can very well do it with below code, i am giving you just the code to insert xml hope u are done with rest of other things..
import oracle.xdb.XMLType;
//now inside the class......
// this will be to convert xml into string
File file = new File(your file path);
FileReader fileR = new FileReader(file);
fileR.read(data);
String str = new String(data);
// now to enter it into db
conn = DriverManager.getConnection(serverName, userId, password);
XMLType objXml = XMLType.createXML(conn, str);
// inside the query statement put this code
objPreparedstatmnt.setObject(your value index, objXml);
I have done like this and it is working fine.
The easiest way is to simply use the
stmt.setString(position, xml);
methods (for "small" strings which can be easily kept in Java memory), or
try {
java.sql.Clob clob =
oracle.sql.CLOB.createTemporary(
connection, false, oracle.sql.CLOB.DURATION_SESSION);
clob.setString(1, xml);
stmt.setClob(position, clob);
stmt.execute();
}
// Important!
finally {
clob.free();
}
Source: Stackoverflow.com