temporary tables in Java procedures (Oracle DB)

Question

I have a problem with using temporary tables for storing output results of my java procedure inside Oracle DB.

Connection connection =
DriverManager.getConnection("jdbc:default:connection:");
...
st.executeUpdate("CREATE GLOBAL TEMPORARY TABLE TT1 (id NUMBER, name VARCHAR2(10)) ON COMMIT DELETE ROWS");
st.executeUpdate("INSERT INTO TT1 VALUES (1, 'Scott')");
...

After executing procedure I want to manage the data from temporary table using SQL code, but temporary table "doesn't exist".Is it erased because i made commit in java?

I tried create table with ON COMMIT PRESERVE ROWS, but have same result. Is it because java is running in another session?

It's because the temporary table only lasts as long as your connection is connected. I suggest transferring all the db work to an Oracle stored procedure and just send the final results to java. — Dan Bracuk
The table isn't dropped by the commit, but the data in it will be deleted, because of the ON COMMIT DELETE ROWS clause. It is also only visible to the same session so with PRESERVE ROWS you still might not see the data if you're using a connection pool and/or releasing the connection in between. It is unusual to really need a GTT though, and if you do really need one you should not create it at runtime. Look into using collections instead, but it isn't clear what you're really trying to do. — Alex Poole

Gauri Zope Gauri Zope · Accepted Answer · 2020-06-04T09:00:17

Actually, there are 2 types of temporary table.. 1.Transaction specific 2.Session Specific

In above example, you are using transaction specific temporary table as u commit changes to DB table gets Deleted from temporary table.
and when u create table with ON COMMIT PRESERVE ROWS ,then you are dealing with session specific tables , so changes u need to do in same session

temporary tables in Java procedures (Oracle DB)

1 Answers