Home » SQL & PL/SQL » SQL & PL/SQL » Global Temporary Table and Autonomous_Transaction (Oracle Database, 11g, UNIX)
Global Temporary Table and Autonomous_Transaction [message #590168] Mon, 15 July 2013 10:37 Go to next message
itralarcon
Messages: 36
Registered: February 2011
Location: San Salvador
Member

Hello, i´m expecting an issue because using some PL/SQL Procedures.

The actual flow, works on this way:

  1. The Procedure A extracts and filter some data from the DW, this data is stored on the Global Temporary Table.
  2. Another Procedure, named B, use the data from the Global Temporary Table and store it on a normal table using another procedure Named X that Merge the data from Global Temporary against the Normal Table (inserting if not exist and updating some fields if exist).


(X isn´t important on the new flow)


Now, i need to add some steps on the normal flow:

  1. The Procedure A extracts and filter some data from the DW, this data is stored on the Global Temporary Table.
  2. Another Procedure, named B, use the data from the Global Temporary Table and store it on a normal table.
  3. Using the Data from Global Temporary Teble i must to Store some fields on another normal table, for this i use another Procedure named C that merge the data from Global Temporary Table against the data from normal table, and i must to commit at this point.
  4. X Merge the data from Global Temporary Table and the data from the Normal table con the procedure "C" against another Normal Table (inserting if not exist and updating if exist).


The issue that i´m expecting is that i can´t use "C" for merge and commit, because this truncate the data on the global temporary table. I can´t change the on commit delete rows option, because another procedures are using this Global Temporary Table on production.

Before you ask, i try using AUTONOMOUS_TRANSACTION on "C" and didn´t works because "C" can´t found data on the Global Temporary table ( i use a select count on "C" from Global Temporary), this is because The Autonomous_Transaction (i think). So, what i can do? I tried to trace the session Number on C and A and with the AT is the same ( so isn´t session change problem).

I need:
Commit on the Procedure "C" without Truncating Global Temporary Table because i need this data for X.






Re: Global Temporary Table and Autonomous_Transaction [message #590169 is a reply to message #590168] Mon, 15 July 2013 10:46 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Create a new gtt that's on commit preserve rows and use that instead.
Re: Global Temporary Table and Autonomous_Transaction [message #590170 is a reply to message #590168] Mon, 15 July 2013 10:46 Go to previous messageGo to next message
BlackSwan
Messages: 22793
Registered: January 2009
Senior Member
We can't change how Oracle behaves.
Since it is your application, you get to decide when to issue COMMIT, but accept the consequences for doing so.
We can't change how Oracle behaves.

so what exactly do you expect/desire from us?
Re: Global Temporary Table and Autonomous_Transaction [message #590171 is a reply to message #590170] Mon, 15 July 2013 10:56 Go to previous messageGo to next message
itralarcon
Messages: 36
Registered: February 2011
Location: San Salvador
Member

cookiemonster:
Thnak You!
It´s a good idea, but creating tables on production enviroment it`s a little complicated here on my work (because of DBA's politics). Can be a Plan B.

BlackSwan:
I know i cant change Oracle Behaves.
I`m expecting some ideas for do what i need. I think this is the reason of forums knowledge sharing right?
Re: Global Temporary Table and Autonomous_Transaction [message #590172 is a reply to message #590171] Mon, 15 July 2013 11:03 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Think you'll find plan B is plan A.
You can do that or you can rewrite the whole process to avoid using gtts at all.
Which do you think is less work?
Re: Global Temporary Table and Autonomous_Transaction [message #590173 is a reply to message #590172] Mon, 15 July 2013 11:07 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Or rewrite the other existing stuff that relies on the gtt being on commit delete to work with on commit preserve.
Re: Global Temporary Table and Autonomous_Transaction [message #590174 is a reply to message #590173] Mon, 15 July 2013 11:39 Go to previous messageGo to next message
itralarcon
Messages: 36
Registered: February 2011
Location: San Salvador
Member

Thanks!
Yep, you're completely right. I think i have less options.
Re: Global Temporary Table and Autonomous_Transaction [message #590177 is a reply to message #590174] Mon, 15 July 2013 13:05 Go to previous message
Bill B
Messages: 1099
Registered: December 2004
Senior Member
Had the same problem and it is easy to handle. Set up the GTT to preserve on commit and simply do a delete on the GTT before the commit where you need to. Since the data is most likely all in memory and no redo the delete is very fast.
Previous Topic: Temp Table within an Stored Procedure
Next Topic: overlap data need
Goto Forum:
  


Current Time: Sun Sep 21 15:27:09 CDT 2014

Total time taken to generate the page: 0.08215 seconds