/

import java.sql.*;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleResultSet;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.pool.OracleDataSource;

import oracle.sql.NCLOB;

public class Class2 {
public static void main(String[] args) throws Exception {
String sql1_Syntax = "select to_nclob('a') from dual";
String sql2_Syntax = "select * from v$tempseg_usage";
String sql3_Syntax = "select count(*) from v$tempseg_usage";
String sql4_Syntax = "alter session set events '60025 trace name context forever'";

Connection conn = getConnection();

// event 60025 - if there are no active temp lobs in the session (ie: both cache temp lob and
// no-cache temp lobs used are zero) then the temp segment itself will also be freed
Statement stmt = conn.createStatement();
System.out.println(sql4_Syntax);
stmt.executeUpdate(sql4_Syntax);

System.out.println(sql1_Syntax);
ResultSet rs = stmt.executeQuery(sql1_Syntax);
NCLOB nclob;
rs.next();
System.out.println(rs.getString(1));
nclob = (NCLOB) ((OracleResultSet) rs).getNClob(1); /* we now have handle to the LOB memory */

System.out.println(sql2_Syntax);
rs = stmt.executeQuery(sql2_Syntax);
while(rs.next()) {
System.out.println(rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3) + " " + rs.getString(4) + " " + rs.getString(5) + " " + rs.getString(6) + " " + rs.getString(7) + " " + rs.getString(8));
}


// System.out.println("disconnect"); // We are NOT disconnecting
// conn.close();
// conn = getConnection();

 nclob.free();

//nclob.freeTemporary();

// this block does the same as nclob.freeTemporary();  or nclob.free()
//OracleCallableStatement cs = (OracleCallableStatement) conn.prepareCall("begin DBMS_LOB.FREETEMPORARY ( ? ); end;");
//cs.registerOutParameter(1,OracleTypes.NCLOB);
//cs.setNClob(1,nclob);
//cs.execute();


stmt = conn.createStatement();
System.out.println(sql2_Syntax);
rs = stmt.executeQuery(sql2_Syntax);
while(rs.next()) {
System.out.println(rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3) + " " + rs.getString(4) + " " + rs.getString(5) + " " + rs.getString(6) + " " + rs.getString(7) + " " + rs.getString(8));
}
ResultSetMetaData rsmd = rs.getMetaData();
rs = stmt.executeQuery(sql3_Syntax);
rs.next();
// if # of rows is zero,this means temp segments have been released
System.out.println("Nb of rows : " + rs.getString(1));

}

public static Connection getConnection() throws sqlException {
String username = "scott";
String password = "tiger";
String thinConn = "jdbc:oracle:thin:@//host/ORCL";
OracleDataSource ods = new OracleDataSource();
ods.setUser(username);
ods.setPassword(password);
ods.setURL(thinConn);
Connection conn = ods.getConnection();
DatabaseMetaData dbmd = conn.getMetaData();
System.out.println(dbmd.getDatabaseProductVersion());
System.out.println(dbmd.getDriverVersion());
conn.setAutoCommit(false);
return conn;
}
}