I am creating a global temporary table on commit delete rows and then do a select right after. The rows disappear from the global temporary table. I read that only a commit or end session delete the rows, but not in my case.
I looked up toad setting and found Auto Commit is unchecked under options->Oracle->Transactions. So, what am I missing. I also tried "set autocommit off" prior to the execute script and didn't work either. The status bar does not show auto commit either. Thanks for your help.
Maybe your insert and select are in different sessions? Set these options as shown
Hi John,
Thanks for replying. My settings are exactly the same. this is the order of my transactions
create global temporary table on commit delete rows as
select ...
select from table.
No rows return, but the select by itself works fine.
I'll check with my IT department and let you know if something is wrong on their end.
John
I think I see what's happening.
As you probably know, when DDL is performed, Oracle automatically commits.
So when I do a "create global temporary table gtt_tables as select * from dba_tables", the table is created, and it kind of self-empties because of the implicit commit.
When I do an "insert into gtt_tables select * from dba_Tables", then "select * from gtt_tables", I get data back.
1 Like
Dang! The implicit commit got me.
Thanks John
