Home » SQL & PL/SQL » SQL & PL/SQL » Temporary Tables
Temporary Tables [message #310983] Thu, 03 April 2008 03:00 Go to next message
saharookiedba
Messages: 56
Registered: September 2007
Location: PUNE
Member
Hi,
I have a stored procedure where I have a requirement wherein i need to store some data in a table, this data should be temporary i.e every time the Procedure runs fresh data should be inserted..
My Question is --
should i create a Global Temporary Table in Database like

CREATE GLOBAL TEMPORARY TABLE my_temp_table (  column1  NUMBER,  column2  NUMBER) ON COMMIT DELETE ROWS;


I am writing the Queries as Autonomous_Transaction, so will the data in the above table be erased on Commit in Procedure.
OR

I need to Create temporary table inside the Procedure..

Please advice me on this issue..
Re: Temporary Tables [message #310989 is a reply to message #310983] Thu, 03 April 2008 03:12 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ A table definition is static, you don't create and drop it on the fly
2/ If you use an autonomous transaction (which is likely a bad design) then you can't fill and query the table in the different part if you have "on commit delete rows", you then must have "on commit preserve rows" but in this case you have to manage the rows.

In short, if you are in the case you have to post this question then your design is bad.

Regards
Michel
Previous Topic: Cursor Question
Next Topic: Need help with passing parameter to procedure
Goto Forum:
  


Current Time: Sat Dec 03 05:59:45 CST 2016

Total time taken to generate the page: 0.31309 seconds