Package com.freemindcafe.jdbc.sample3

global temporary table with on commit delete rows; 1) create table using basicDataSource and then resuse 2) Create temporary table using jdbcTemple and then reuse 3) Create table using jdbcTemplate via sql script file and then reuse More...

Classes
class	ExecuteDDLAndUseTableTest

Detailed Description

global temporary table with on commit delete rows; 1) create table using basicDataSource and then resuse 2) Create temporary table using jdbcTemple and then reuse 3) Create table using jdbcTemplate via sql script file and then reuse

Concepts

• sqlplus login opens up a connection and session. We can close the session by doing a disconnect;
• We can again open the session by doing a connect;
• Two sqlplus processes means two connections and two sessions.
• If we do disconnect on the two sessions then we will have two connections but no sessions .
• The table is visible across the sessions.
• Data will not be visible across the sessions.
• Because of 'on commit delete rows', the data will not be available after the transaction is done.

Steps

• Open two sqlplus sessions.
• Run the following in any session to make sure that we start in clean state
  truncate table tab_tmp;
  drop table tab_tmp;
• Run the following in First session
  create global temporary table tab_tmp (name VARCHAR2(30)) on commit delete rows;
• Run the following in Second session
  select * from tab_tmp;
  Second session can see the table
• Run the following in First session
  insert into tab_tmp values('abc');
• Run the following in First session
  select * from tab_tmp;First session can see the row
• Run the following in Second session
  select * from tab_tmp;
  Second session can't see the row
• Run the following in First session
  disconnect;
• Run the following in First session
  connect;
• Run the following in First session
  select * from tab_tmp;First session can't see the row
• Run the following in First session
  show autocommit;autocommit should be off
• Run the following in First session
  insert into tab_tmp values('abc');
• Run the following in First session
  commit;
• Run the following in First session
  select * from tab_tmp;First session can't see the row as the data is not available after commit