General discussion

Locked

CLOB data insertion into orace table thr

By ssudha ·
Please give the code for inserting CLOB data into oracle through java code.First I insert "empty_clob()" into the table and then get one refrence through getCLOb and opened DataOutputStream and then write the required string into the stream.But whenI retrived the object and displayed it,It is displayed as "null".

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

CLOB data insertion into orace table thr

by drprabhu In reply to CLOB data insertion into ...

1. First insert the empty_CLOB() into the Clob field of Oracle database.
2. Then get the reference to the Clob field through getObject() and type cast it to (oracle.sql.CLOB) which is present in
classes12.zip file
3. Open the character O/P stream with the Clob field
4. Write the Character array to the Writer Object

CODE:(put classes12.zip in the classpath.)
PrintWriter writerInsert = null;
String strXmlTree; //input string for Clob field
String sql = "INSERT into T_WS_WORKSPACE_TREE";
sql = sql +" (WS_XML)";
sql = sql +" VALUES (empty_CLOB())";
System.out.println("QUERY : "+sql);
try {
Statement stmt = con.createStatement();
int intTemp = stmt.executeUpdate(sql);
System.out.println("Record Inserted with Empty CLOB");

String strSQLQry = "Select WS_XML from T_WS_WORKSPACE_TREE where WS_USER_ID='"+strUserId+"' AND WS_TREE_TYPE ='"+strTreeType+"' FOR UPDATE";
Statement stmtXML = con.createStatement();
ResultSet rsXML = stmtXML.executeQuery(strSQLQry);
if(rsXML.next()) {
// get the data obtained from the query
oracle.sql.CLOB clob = (oracle.sql.CLOB)rsXML.getObject(1);
// Using the object as a handle, update the column as follows:
writerInsert = ((oracle.sql.CLOB)clob).getCharacterOutputStream();

// find the length of input data
byte [] byteData = strXmlTree.getBytes();
int intIPLength = byteData.length;

// convert byte array to char array
char[] charData = new char[intIPLength];
for(int i=0; i<intIPLength; i++) {
charData = (char) byteData;
}// for()
// closing the writer
writerInsert.write(charData);
writerInsert.flush();
writerInsert.close();
}// if()
catch (Exception e) {
if (con != null && !con.isClosed()) {
con.rollback();
con.close();
throw e;
}// if
}// catch
finally {
con.close();
} // finally

Collapse -

CLOB data insertion into orace table thr

by drprabhu In reply to CLOB data insertion into ...

CODE TO READ THE CLOB FIELD

String sqlCompQry = "SELECT ac_compound_struct,ac_molecular_wt,ac_svg_struct,ac_svg_box_width,ac_svg_box_height FROM T_ALL_COMPOUNDS WHERE AC_COMPOUND_ID = ?";

System.out.println("QUERY :: "+sqlCompQry);

PreparedStatement ppdStmt = m_dbHelper.getConnection().prepareStatement(sqlCompQry);
ppdStmt.setLong(1,compoundId);
ResultSet rs = ppdStmt.executeQuery();


while (rs.next()) {
java.sql.Clob clob=rs.getClob(1);
long i=1;
int j=(int)clob.length();
String strStructure = clob.getSubString(i,j);
}

Back to Web Development Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums