drop table Clob_to_Long_Test  purge;

create table Clob_to_Long_Test (col1 number, clob_column clob, long_column long, log_date date);
declare 
  l_biggie_1  varchar2(32767) :=  rpad('a', 32766, 'b');
  l_biggie_2  varchar2(32767) :=  rpad('1', 32766, '2');
  l_clob      clob            :=  l_biggie_1 || l_biggie_2;
begin 
  insert into Clob_to_Long_Test values (1, l_clob, null, null);
  commit;
end;
/

create or replace and compile java source named "ClobConverter" 
as

import java.lang.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
import org.apache.*;
import java.reader.*;
import java.io.*;

public class ClobConverter
{
  public static void UpdateClob (int in_column_id) throws SQLException
  {
  
    Connection con = null;
    OracleDriver ora = new OracleDriver(); 
    con = ora.defaultConnection(); 

    Clob myClob = null;
    String sql1 = "select clob_column, dbms_lob.getlength(clob_column) as the_length from Clob_to_Long_Test where COL1 = ?";
    int theclobsize=-1;
    
    PreparedStatement pstmt = con.prepareStatement(sql1);
    pstmt.setInt(1, in_column_id);
    ResultSet rs = pstmt.executeQuery();
    if (rs.next()) 
    {
        myClob = rs.getClob("clob_column");    
        theclobsize = rs.getInt("the_length");
    }
    pstmt.close();
    
    final StringBuilder builder = new StringBuilder(theclobsize);
    final BufferedReader reader = new BufferedReader(myClob.getCharacterStream());

    try 
    {
        for (String line = null; (line = reader.readLine()) != null; ) 
        {
            builder.append(line);
        }
    } 
    catch (IOException e) 
    {
        e.printStackTrace();
    }
  
    String sql2 = "update Clob_to_Long_Test  set long_column = ? , log_date= sysdate where COL1 = ?";
    try
    {
      PreparedStatement pstmt2 = con.prepareStatement(sql2);
      pstmt2.setString(1, builder.toString());
      pstmt2.setInt(2, in_column_id);
      pstmt2.executeUpdate();
      pstmt2.close();
    }
    catch (SQLException e)
    {
      System.err.println(e.getMessage());
    }
  }   
}
/


create or replace procedure p_call_ClobConverter (p_column_vlaue in number)
as 
language java name 'ClobConverter.UpdateClob(int)'; 
/

exec p_call_ClobConverter(1);

select *
from Clob_to_Long_Test;