It's not a secret anymore!
The moose likes JDBC and Relational Databases and the fly likes Convert large string to Clob Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Convert large string to Clob" Watch "Convert large string to Clob" New topic

Convert large string to Clob

Alice Zhang

Joined: Mar 07, 2003
Posts: 1
My environment is as following:
Oracle 9.0.1
JDBC 2.0
Oracle thin driver
Sun Solaris sparc, OS 9
JDK 1.2
The question is I can not update a Clob before saving it. The input from our web application users sometime can be large, from 100k bytes to MBs. I want to save user's input into CLOB datatype in our Oracle database. Since, I am using JDK1.2, I do not have access to oracle.sql.clob. Do you know how to convert a string to a Clob in Java program and how to prepare a statement before executing a query. I used preparedStatement.setAsciiStream(int index, InputStream x, int length) to prepare my statement, and it doesn't work. I got an error thrown saying Io exception: End of TNS data channel.
Thank you very much for your help.
A Tavouille

Joined: May 12, 2004
Posts: 3
To create a Clob, use this instruction :
CLOB newClob = CLOB.createTemporary(conn, false, CLOB.DURATION_CALL);
.... but don't work with Websphere Datasource
Another solution : insert a 'empty_clob()' in table, then select and update the clob :
Insert: "insert into mytable(primarykey, clob_col) values (?, empty_clob())";
Then select and lock this row : " select clob_col from mytable where primarykey = ? for update"
Get the cob : oracle.sql.CLOB clob=(CLOB)rs.getClob("clob_col")
And update the clob content : fillClob(clob,"xxxx xknsd")
With the fillClob method :
// Utility function to put data in a Clob
static void fillClob(CLOB clob, String value) throws Exception {
Writer outstream = clob.getCharacterOutputStream();
outstream.write(value, 0, value.length());
A Tavouille

Joined: May 12, 2004
Posts: 3
This method work nice
* Creer un Clob contenant la valeur pass� en param�tre. Fonctionne uniquement avec Oracle.<br>
* <b><i>Important </i>: Penser � fermer le clob une fois utilis� (ie apres maj ou insert en base)</b>
* <i>Note </i>: Sur Oracle 10g, on peut utiliser la property SetBigStringTryClob=true<br> et utiliser directement ps.setString(i,value)<br>
* dans le Dao<br>
* @param conn
* @param value
* @return Clob
* @TODO gerer de facon non proprietaire 'Oracle' des que possible.
protected static Clob createClob(Connection conn,String value) throws SQLException{
CallableStatement stmt = null;
//Creation d'un Clob temporaire
stmt = conn.prepareCall("{ call DBMS_LOB.CREATETEMPORARY(?, TRUE)}");
stmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CLOB);
CLOB newClob = (CLOB)stmt.getObject(1);

return newClob;
Bear Bibeault
Author and ninkuma

Joined: Jan 10, 2002
Posts: 63346

"alexis alexis",
We're pleased to have you here with us in the JDBC forum, but there are a few rules that need to be followed, and one is that proper names are required. Please take a look at the JavaRanch Naming Policy and adjust your display name to match it.
In particular, your display name must be a first and a last name separated by a space character, and must not be obviously fictitious.
Forum Bartender

[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
I agree. Here's the link:
subject: Convert large string to Clob
jQuery in Action, 3rd edition