Win a copy of Hands On Software Engineering with Python this week in the Jython/Python forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Bear Bibeault
  • Knute Snortum
  • Liutauras Vilda
Sheriffs:
  • Tim Cooke
  • Devaka Cooray
  • Paul Clapham
Saloon Keepers:
  • Tim Moores
  • Frits Walraven
  • Ron McLeod
  • Ganesh Patekar
  • salvin francis
Bartenders:
  • Tim Holloway
  • Carey Brown
  • Stephan van Hulst

Temporary tables (Oracle) and web based apps  RSS feed

 
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I have a couple of questions about using Oracle temporary tables with JDBC.
The plan is to aggregate a whole lot of data into a temporary table. Once that table is populated the user will be able to run a few different reports off it.

Now, this will be done as web application running on tomcat and the database is a Oracle 10g.

The problem I'm having is that the data in the table is truncated as soon as the connection is closed. This kind of makes sense since it is available only is a session or a transaction. The only solution i can think of is to try to keep the connection open across requests. But that does not seem like a good idea...?

So does that mean that using temporary tables in a web application is not useful? (Unless you query the table in the same request cycle as you populate it).
Has any one else ran into this problem? is there a solution or a nice workaround?

thanks!

Jan
 
author & internet detective
Posts: 39054
714
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Originally posted by Jan Andersson:
The only solution i can think of is to try to keep the connection open across requests. But that does not seem like a good idea...?


You are correct that this is not a good idea. Keeping the connection open places a huge drain on the system. I would look for an alternate approach.

One I can think of:
1) Create a logically "temporary" regular table called userid_temp_table for each user.
2) Populate it and access it the way you did your temp table.
3) Run a job every so often to delete userid_xxx tables that haven't been used in a certain length of time.
 
Always look on the bright side of life. At least this ad is really tiny:
RavenDB is an Open Source NoSQL Database that’s fully transactional (ACID) across your database
https://coderanch.com/t/704633/RavenDB-Open-Source-NoSQL-Database
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!