Home » SQL & PL/SQL » SQL & PL/SQL » How to achieve Global Temporary Functionality by using the Table (oracle 10g,windows)
How to achieve Global Temporary Functionality by using the Table [message #301197] Tue, 19 February 2008 08:56 Go to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi,

I Have a procedure which calculates/compute certain values and then insert that values in to a Global Temparary table.
And i will do some more transaction on this temp table and finally sends a ref cursor by queying this tem table.

Since, this Global Temp table is on Commit Preserve Row I had No problem.

But, When it is Moved to the another Env where they are using the connection Pool there the problem started.

Is it Possible to achieve the same functionality in any other way ?
I mean even If i use coonection pool the data in the temp table should be refreshed every time it runs.?
Re: How to achieve Global Temporary Functionality by using the Table [message #301199 is a reply to message #301197] Tue, 19 February 2008 09:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to use a permanent table if you does not keep the same session.

Regards
Michel
Re: How to achieve Global Temporary Functionality by using the Table [message #301203 is a reply to message #301197] Tue, 19 February 2008 09:23 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
But I don't want to keep all those rows as these are transaction oriented.


Re: How to achieve Global Temporary Functionality by using the Table [message #301215 is a reply to message #301203] Tue, 19 February 2008 11:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are inconsistencies in your post.
If you use connection pool that makes you change the session then it changes the transaction and then you can't this design.

You have to choose, either connection pool either GTT but not both.

Regards
Michel
Re: How to achieve Global Temporary Functionality by using the Table [message #301249 is a reply to message #301197] Tue, 19 February 2008 16:50 Go to previous message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
I think you must be missing something or haven't told us something. It seems to me that connection pooling should be irrelevant. Let's say you don't have connection pooling. Then you would have something like the following scenario.

Client opens a connection (sid = x).
Client calls DB procedure to do your insert/update of global temporary table.
Client calls other DB procedure to further insert/update of global temporary table. (Repeat as necessary)
Client calls procedure to get ref cursor.

That would work. This wouldn't.

Client opens a connection (sid = x).
Client calls DB procedure to do your insert/update of global temporary table.
Client opens a connection (sid = y).
Client calls other DB procedure to further insert/update of global temporary table. (Repeat as necessary)
Client opens a connection (sid = z).
Client calls procedure to get ref cursor.

The only difference with connection pooling is that the connections are already opened, i.e. the sids exist on the DB before the client opens the connection. In a connection pooled environment, you could (and should) still open and use the same session throughout the transaction. The only difference I can see is that you will need to explicitly truncate the global temporary table before you begin each new transaction.

Or am I missing something?
Previous Topic: How to do Implied Commit
Next Topic: group
Goto Forum:
  


Current Time: Thu Dec 08 04:32:03 CST 2016

Total time taken to generate the page: 0.10775 seconds