import java.sql.Connection;
  import java.sql.SQLException;

  import java.sql.DriverManager;
  import java.sql.CallableStatement;
  import oracle.sql.CLOB;

  // IO Imports
  import java.io.Writer;

  import java.io.File;
  import java.io.FileInputStream;
  import java.io.BufferedReader;
  import java.io.FileReader;
  import java.io.FileNotFoundException;

  /*

   * This is a runnable class which demostrates different ways of creating CLOB
   * object for passing it to a database stored procedure. It has two methods:
   * callUpdate() and callUpdateUsingStream. The first one uses temporary CLOB
   * with oracle.sql.CLOB class and the other will create the CLOB object by
   * streaming the content and then passing it to the callable statement.
   */

public class TestClob {


  // Variable for reading id value.
  static String id = null;

  // Variable to read the filename and streaming flag.
  static String  fileName      = null;

  // Variable to read the streaming flag.
  // When true, it will use data streaming for update.

  static String streamingFlag = "true";

  // Create connection object.
  static Connection conn = null;

  /*
   * Default Constuctor that takes no arguments.
   */
  public TestClob() {
  }


  /*
   * Constuctor that takes in values of id, filename and streaming flag.
   */
  public TestClob(String idn, String filename, String flag ) {
    id             = idn;
    fileName       = filename;
    streamingFlag  = flag;
  }

  /*

   * Runnable main method.
   */
   public static void main (String args [])
     throws SQLException, FileNotFoundException {

       TestClob testClob = new TestClob(args[0], args[1], args[2]);
       testClob.getConnection();
       // Depending on the flag, call respective method.
       if (streamingFlag.equals("true")) {
         testClob.callUpdateUsingStream(id, fileName);
       } else  {
           testClob.callUpdate(id, fileName);

       }
   }

   /*
    * Method that gets database connection and returns the same.
    */
    public void getConnection() throws SQLException{
     // Load Oracle driver.
     DriverManager.registerDriver (new oracle.jdbc.OracleDriver());

     try {
       // Connect to the database.
       conn = DriverManager.getConnection

                  ("jdbc:oracle:thin:@incq234a.idc.oracle.com:1521:or9202",
                   "scott", "tiger");
     } catch (SQLException sqlex) {
         System.out.println("SQLException while getting db connection: "+sqlex);
         if (conn != null) {
           conn.close();
         }
     } catch (Exception ex) {
         System.out.println("Exception while getting db connection: "+ex);
         if (conn != null) {
           conn.close();
         }
     }
    }


  /*
   * Method that calls update stored procedure on the database. This will use 
   * the getCLOB method() and oracle.sql.CLOB class to create the CLOB object 
   * before passing it to the procedure.
   */
    public void callUpdate(String id, String filename)
      throws SQLException {

      CallableStatement cs = null;
      CLOB clob = null;
      String clobData = null;
      try {
        // Read the file whose content has to be updated in the CLOB column.
        String lineSep = System.getProperty("line.separator");

        BufferedReader br = new BufferedReader(new FileReader(filename));
        String nextLine = "";
        StringBuffer sb = new StringBuffer();
        while ((nextLine = br.readLine()) != null) {
          sb.append(nextLine);
          sb.append(lineSep);
        }
        // Convert the content to string.
        clobData = sb.toString();

        // call Stored DB procedure for updating CLOB column.
        cs = (CallableStatement)
                conn.prepareCall( "begin updateStory(?,?); end;" );

        // create the CLOB object
        clob = getCLOB(clobData);


        // set id
        cs.setObject(1, id );

        //set clob data
        cs.setObject(2, clob );

        // Execute callable statement.
        cs.execute();

        // Close the Statement object
        cs.close();

      } catch ( SQLException ex ) {
          System.out.println("SQLException status : " + ex.getMessage(  ) );
      } catch ( Exception ex ) {
          System.out.println("some exception " + ex.getMessage(  ) );

      } finally {
        try {
          // Close Statement
          if ( cs != null ) {
            cs.close(  );
          }

          // Free CLOB
          if ( clob != null ) {
            clob.freeTemporary();
          }
          // Close connection
          if (conn !=null) {
            conn.close();
          }
        } catch ( Exception ex ) {
          System.out.println("Some exception in callUpdate method of given "
                                   + "status : " + ex.getMessage(  ) );

        }
      }
    }

  /*
   * This method creates CLOB object using temporary clob and returns the same.
   */
    private CLOB getCLOB( String clobData )
                    throws Exception {
      CLOB tempClob = null;

      try {
        //  create a new temporary CLOB
        tempClob = CLOB.createTemporary( conn, true, CLOB.DURATION_SESSION );

        // Open the temporary CLOB in readwrite mode to enable writing
        tempClob.open( CLOB.MODE_READWRITE );

        // Get the output stream to write

        Writer tempClobWriter = tempClob.getCharacterOutputStream( );

        // Write the data into the temporary CLOB
        tempClobWriter.write( clobData );

        // Flush and close the stream
        tempClobWriter.flush(  );
        tempClobWriter.close(  );

        // Close the temporary CLOB
        tempClob.close(  );

      } catch ( Exception exp ) {
          tempClob.freeTemporary();
          System.out.println("Exception thrown in getCLOB method "
                             + "of given status : "
                             + exp.getMessage(  ) );
      }
      return tempClob;
    }


   /*
    * This method uses CallableStatement.setAsciiStream() method to stream
    * the clob data to the procedure within the callable statement.
    */
    public void callUpdateUsingStream(String id, String filename )
       throws SQLException, FileNotFoundException {
    CallableStatement cs = null;
    try {

      conn.setAutoCommit(false);
      String fileName = filename;

      // Open the sample file as a stream 
      File file = new File(fileName);
      FileInputStream fin = new FileInputStream(file);

      // Call Stored DB procedure for updating clob column
      cs = (CallableStatement)
                  conn.prepareCall( "begin updateStory(?,?); end;" );


      // set id.
      cs.setObject(1, id );

      // set the clob parameter using setAsciiStream method.
      cs.setAsciiStream(2,fin,(int)file.length());

      cs.execute();
      conn.setAutoCommit(true);
      
    } catch ( SQLException sqlex ) {
          System.out.println("SQLException in callUpdateUsingStream method "+
                             " of given status : " + sqlex.getMessage() );
    } catch ( FileNotFoundException fnex ) {
          System.out.println("FileNotFoundException in callUpdateUsingStream method "+
                             " of given status : " + fnex.getMessage(  ) );
    } finally {
        try {
          // Close Statement
          if ( cs != null ) {
            cs.close(  );
          }
          // Close connection.

          if (conn !=null) {
            conn.close();
          }
        } catch ( Exception ex ) {
          System.out.println("Some exception in callUpdateUsingStream method "+
                             " of given status : " + ex.getMessage(  ) );
        }
      }
  }
}