creating temporary tables using java code

[vijay jamadade]

Hi Ranchers,

I want to implement temporary table functionality of oracle in my application. Please tell me how I can create temporary global tables using java code connection object. like as shown below

  Create global temporary table tempTable
(
id number,
value number
);   

which if i try to use through connection object gives error

  

java.lang.NullPointerException
	oracle.jdbc.driver.T4C8Oall.getNumRows(T4C8Oall.java:870)
	oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:952)
	oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
  

Tell me how to implement these session tables in oracle?

Thanks in advance

[Paul Sturrock]

Can you show us your JDBC code?

[vijay jamadade]

	
StringBuffer tempTableQuery=new StringBuffer();
tempTableQuery.append("Create global temporary table test(id number,value number)");
			stmt = conn.createStatement();
			rs = stmt.executeQuery(tempTableQuery.toString()); 

Its getting created now but i don't understand how to use it. It is not getting removed after my session expires. Can you tell me how this temporary table functionality works. My requirement is i want to upload some data in this table in one session and if all is good then i want to move this data to the permanent table afterwards. Can you explore little bit more on this scenario.

[Paul Sturrock]

With global temporary tables in Oracle only the data is temporary.

[vijay jamadade]

here in case of temporary table which session is going to consider? And how the session specific data gets deleted because i cleared my session by

session.clear()

java statement in logout but i am still getting the data in the temporary table in java code. How to delete that data.

[vijay jamadade]

I mean how can i end the database session using java. Because when i restart the server then only the table data gets deleted. Can we do it in source after finishing my transaction?

[Paul Sturrock]

vijay jamadade wrote:here in case of temporary table which session is going to consider? And how the session specific data gets deleted because i cleared my session by

session.clear()

java statement in logout but i am still getting the data in the temporary table in java code. How to delete that data.

What session is that? Are you running Java code in the database?

[vijay jamadade]

Hi Paul,

I have created a struts 2 application through which i am accessing a temporary table that i have created in database. What happens is i insert some data in that and after that i move the data using same application to the permanent table. Now my question is how the data in the temporary table gets deleted using my struts 2.0 application. It gets deleted only when i restart my tomcat server. Do I need to delete the data firing a query through my application itself or is there any functionality provided by temporary tables so that it can delete the data after i move it to the permanent table?

Waiting for your reply. Thanks.

[Paul Sturrock]

So the session you are clearing is an HTTPSession? This will do nothing to the data in the temporary table in Oracle. Global temporary tables work that data create in one is deleted when the Oracle session that created it is ended. In a web app. you probably don't have complete control over when this will happen, assuming you are using a connection pool? I'd manually handle it in your application instead.

[vijay jamadade]

Thank you so much Paul.

Until now i was doing the same. I though there is some way thats why i was discussing regarding that. Fine now.