Home » SQL & PL/SQL » SQL & PL/SQL » Gloabal Temporary Table (Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production)
Gloabal Temporary Table [message #575196] Mon, 21 January 2013 04:08 Go to next message
sgollapudi
Messages: 9
Registered: January 2013
Location: HYDERABAD
Junior Member
Hi All,

Global temporary tables data is session specific. But I am able to access from pragma autonomous_transaction,
As pragma autonomous_transaction creates a new session then how can it is able to accessing the gtt data.

Thanks in advance.
sgollapudi.

Please find below code.

create global temporary table test_preserve( a number) on commit preserve rows
/

create or replace procedure sp1 as
pragma autonomous_transaction;
a number;
begin
select count(*) into a from test_preserve;
dbms_output.put_line('a '||a);
commit;
end;
/


create or replace procedure sp2 as
begin
insert into test_preserve values (100);
commit;
sp1;
end;
/

set serveroutput on

begin
sp2();
end;
/

a 1
Re: Gloabal Temporary Table [message #575197 is a reply to message #575196] Mon, 21 January 2013 04:26 Go to previous messageGo to next message
Michel Cadot
Messages: 57611
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:

Michel Cadot wrote on Fri, 18 January 2013 10:41
From your previous topics:

cookiemonster wrote on Tue, 15 January 2013 14:51
Please read and follow How to use [code] tags and make your code easier to read?
...


sgollapudi wrote on Thu, 17 January 2013 16:27
...
*BlackSwan added {code} tags. Please do so yourself in the future.

...


Quote:
As pragma autonomous_transaction creates a new session then how can it is able to accessing the gtt data.


It does not create a new session, it creates a new transaction.
See ON COMMIT PRESERVE ROWS option of GTT.

Regards
Michel


Re: Gloabal Temporary Table [message #575198 is a reply to message #575196] Mon, 21 January 2013 04:27 Go to previous message
ThomasG
Messages: 3064
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
sgollapudi wrote on Mon, 21 January 2013 11:08

As pragma autonomous_transaction creates a new session


Where did you get that information? There is nothing in the Oracle Documentation of pragma autonomous_transaction that mentions "sessions"
Previous Topic: Checking values of procedure's parameters
Next Topic: Join two permanent tables with condition
Goto Forum:
  


Current Time: Fri Apr 18 13:43:01 CDT 2014

Total time taken to generate the page: 0.07037 seconds