i want to read a clob data from database and put it into another file

cold you please give me the code for that

Another option you can find here:
read clob to string
 http://maclochlainn.wordpress.com/how-to-read-a-clob-through-jdbc/

i dont want to convert into stringin sql query

That is how I do it in my real program.
It is still a little bit oracle specific
as it uses OracleResultSet class.
And if you defgine FileWriter instead of CharacteArrrayWrite you ca write it to file,
or write the resulting string to file

In the link which I provided above it gives more general way

   PreparedStatement pstmt = connection.prepareStatement(""select clob_filed from mytable where index = ?");
pstmt.setInt(1,1);

        ResultSet results=pstmt.executeQuery();

        if(results.next())
        {
           CLOB clob = ((OracleResultSet)results).getCLOB(1);

            if(clob == null) return null;

        Reader reader=clob.getCharacterStream();
        CharArrayWriter writer=new CharArrayWriter();
        int i=-1;

        while ( (i=reader.read())!=-1)
        {
        writer.write(i);
        }
    //    System.out.println("CLOB data read from Oracle: "+new String(writer.toCharArray()));
            String resultStringFromClob =    new String(writer.toCharArray());
        }

    }

Select allOpen in new window

This is how they do it in that link - they have  a little bit
too many words there - but this is the essence - they use getClob(colimn) method
of the ResultSet

Statement stmt = conn.createStatement();

stmt.executeQuery("SELECT item_desc " +
"FROM   item " +
"WHERE  item_title = 'The Lord of the Rings - Fellowship of the Ring'" +
"AND    item_subtitle = 'Widescreen Edition'");


Clob clob = rset.getClob(1);
// Check that it is not null and read the character stream.
if (clob != null) {
Reader is = clob.getCharacterStream();
// Initialize local variables.
sb = new StringBuffer();
length = (int) clob.length();
// Check CLOB is not empty.
if (length > 0) {
// Initialize control structures to read stream.
buffer = new char[length];
count = 0;
// Read stream and append to StringBuffer.
try {
while ((count = is.read(buffer)) != -1)
sb.append(buffer);
}

Select allOpen in new window

           psmt = getPreparedStatementInstance(CommonpowerConstants.InwardRemittance_GET_MESSAGE_DETAIL_SQL);
            crs =psmt.executeQuery();

it would provide numberformat Exception when executing query

my query is select rawmessage rawMessage, mt_info_pk messageNumber from message_info;

raw message is clob field anything i want to change

Please, post more of your code - what is the contents of that string you send to PrepStatement

and also poste the exception stackTrace

add
System.out.println(
CommonpowerConstants.InwardRemittance_GET_MESSAGE_DETAIL_SQL);
before you execute query and post what it prints

when i add tochar method in sql query  my program is working fine.

with out tochar method that is not working

What databse you are using?

to_char may be Oracle specific

Rather let's understand what is the SQL statemet you use in
prepared satement - as I mentioned - printot in your
java program before you prepre statement
printout that   what you feed in to it:

add this to your cose before prearing statement and pots what it prints:
System.out.println(
CommonpowerConstants.InwardRemittance_GET_MESSAGE_DETAIL_SQL);

You can make that a little more generic (see http://technojeeves.com/joomla/index.php/free/51-copying-streams )

Reader in = resultSet.getCharacterStream("columnName");
Writer out = new FileWriter("clob.txt");
IOUtils.copyReader(in, out);

Select allOpen in new window

This is the full program which I now tested, which
reads CLOB form Oraclwe table and writes it to file

It does not use anything Oracle specific
should work with other databases.

import java.io.CharArrayWriter;
import java.io.FileWriter;
import java.io.Reader;
import java.sql.*;

public class ReadFromClob {


    public static void main(String [] args){

        try{
            Class.forName ("oracle.jdbc.driver.OracleDriver");
     Connection conn =
                  DriverManager.getConnection ("jdbc:oracle:thin:@myhost.com:1521:sid","user","pswd");
            PreparedStatement pst = conn.prepareStatement("select t_clob from test_clob where int_key = ?");


    
          pst.setInt(1, 34528);


              ResultSet results=pst.executeQuery();

              if(results.next())
 
                  Clob clob = results.getClob(1);

                  if(clob == null) {
                      System.out.println("clob is null");
                  }

              Reader reader=clob.getCharacterStream();
                  FileWriter fw = new FileWriter("resultFromClob.txt");
            
              int i=-1;

              while ( (i=reader.read())!=-1)
              {
              fw.write(i);
              }
     
     
                  fw.close();
              }

          }
          catch(Exception ex){
              System.out.println("Error " + ex.toString());
              ex.printStackTrace();

          }


       
      }

    
}

Select allOpen in new window