Global Temporary Table & JDBC

Few things to keep in mind

1. if no connection caching is enable, data source only has

OracleDataSource dataSource = new OracleDataSource();
dataSource.setURL("jdbc:oracle:thin:@IP:1521:ent10");
dataSource.setUser("asanga");
dataSource.setPassword("asa");

and if the global temporary table (GTT) is created with on commit preserve,
when the connection is closed con.close() the GTT is cleared and subsequent connections will not see any rows.

2. if connection caching is enabled and GTT is on commit preserve rows

Properties prop = new Properties();
prop.setProperty("MinLimit", "4"); 
prop.setProperty("MaxLimit", "10");
prop.setProperty("InitialLimit", "5"); 
dataSource.setConnectionCacheProperties(prop);
dataSource.setConnectionCachingEnabled(true);

even after closing the connection con.close() subsequent connection will see the rows inserted by the previous connections.

everything below applies to connection created with a pool, connection caching enabeld.

3. if GTT is on commit delete rows then better setAutoCommit(false) otherwise after insert no rows will be visible.

4. if GTT is on commit delete then committing connection will clear the table and others will not see any rows but will add IO

5. if GTT is on commit preserve then inserting rows within a PL/SQL function still makes rows visible for the subsequent connections

6. if GTT is on commit delete then inserting rows within a PL/SQL will only be visible within the PL/SQL block. The outside java connection will not see any rows and also any subsequent connections.


example codes

for on commit preserve

create global temporary table 
temp(a varchar2(10), b number) 
on commit preserve rows;

for on commit delete

create global temporary table 
temp(a varchar2(10), b number) 
on commit delete rows;

function

create or replace function proc return number as
rowscount number;
begin
   for i in 1 .. 100
   loop
     insert into temp values ('aaa '||i, i);
   end loop;
select count(*) into rowscount from temp;
return rowscount;
end;
/       

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.logging.Level;
import java.util.logging.Logger;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.pool.OracleDataSource;


public class RowSetTest {

    public static void main(String[] args) {

        new RowSetTest().test();
    }

    void test() {

        try {

            java.util.Properties prop = new java.util.Properties();
            prop.setProperty("MinLimit", "4"); // the cache size is 5 at least
            prop.setProperty("MaxLimit", "10");
            prop.setProperty("InitialLimit", "5"); // create


            OracleDataSource dataSource;
            dataSource = new OracleDataSource();
            dataSource.setURL("jdbc:oracle:thin:@192.168.0.110:1521:ent10");
            dataSource.setUser("asanga");
            dataSource.setPassword("asa");
            dataSource.setConnectionCacheProperties(prop);
            dataSource.setConnectionCachingEnabled(true);


            Connection conn = dataSource.getConnection();

            String fares = " BEGIN ? := proc() ;END; ";

            CallableStatement fares_call = conn.prepareCall(fares);

            fares_call.registerOutParameter(1, OracleTypes.INTEGER);
            fares_call.execute();


            System.out.println("count from plsql " + fares_call.getInt(1));

            PreparedStatement pr;

            System.out.println("befroe close ++++++++++++++++++++++++");
            pr = conn.prepareStatement("select count(*) from temp");
            ResultSet rs = pr.executeQuery();
            if (rs.next()) {
                System.out.println("count " + rs.getString(1));
            }
            rs.close();
            pr.close();
            conn.close();

            System.out.println("after close +++++++++++++++++++++");
            conn = dataSource.getConnection();
            pr = conn.prepareStatement("select count(*) from temp");
            rs = pr.executeQuery();
            if (rs.next()) {
                System.out.println("count " + rs.getString(1));
            }
            rs.close();
            pr.close();

            conn.close();

            System.out.println("after close +++++++++++++++++++++");
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);
            pr = conn.prepareStatement("select count(*) from temp");
            rs = pr.executeQuery();
            if (rs.next()) {
                System.out.println("count " + rs.getString(1));
            }
            rs.close();
            pr.close();
            conn.close();

            System.out.println("after close +++++++++++++++++++++");
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);
            pr = conn.prepareStatement("select count(*) from temp");
            rs = pr.executeQuery();
            if (rs.next()) {
                System.out.println("count " + rs.getString(1));
            }
            rs.close();
            pr.close();
            conn.close();


        } catch (Exception ex) {
            Logger.getLogger(RowSetTest.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}