Re: CREATE SEQUENCE commits transaction

In article <3C8612AC.4AB24524_at_dimedis.de>, Gert says...
>
>Niall Litchfield wrote:
>>
>> I imagine the tables are pre-created, that is they don't get created in code
>> as well. If so it really sould be part of the app install to create the
>> sequence (and an associated trigger/primary key combo) which the app then
>> uses.
>
>Yes, we are about to change the table-creation to this way.
>
>But there comes another question to my mind: What happens if a
>temp-table is created? This is something that should be normal doing it
>inside a transaction, isn't it? Does this result in a commit, too?
>
>Ciao,
>
>Gert

No, temp tables SHOULD NOT be created in a transaction. They too should be created at application installation time. They are persistant objects in the data dictionary (so you can reference them in PLSQL when its compiled for example -- they always exist) but hold transient data.

sounds like you are used to temp tables in another database... Oracle's temporary tables are similar to temp tables in those other databases the main exception being that they are 'statically' defined. You create them once per database, not once per stored procedure in the database. They always exist but appear empty until you put data in them. They may be SESSION based (data survives a commit but not a disconnect/reconnect). They may be TRANSACTION based (data disappears after a commit). Here is an example showing the behaviour of both. I used the scott.emp table as a template:

SQL> create global temporary table temp_table_session   2 on commit preserve rows
  3 as
  4 select * from scott.emp where 1=0
  5 /
Table created.

the ON COMMIT PRESERVE ROWS makes this a session based temporary table. rows will stay in this table until a logoff. Only I can see them though, no other session will ever see 'my' rows even after I commit

SQL> SQL> SQL> create global temporary table temp_table_transaction   2 on commit delete rows
  3 as
  4 select * from scott.emp where 1=0
  5 /
Table created.

the ON COMMIT DELETE ROWS makes this a transaction based temp table. when you commit -- the rows disappear.

SQL> insert into temp_table_session select * from scott.emp; 14 rows created.

SQL> insert into temp_table_transaction select * from temp_table_session; 14 rows created.

we've just put 14 rows into each temp table and this shows we can 'see' them:

SQL> select count(*) from temp_table_session   2 /

  COUNT(*)

        14

SQL> select count(*) from temp_table_transaction   2 /

  COUNT(*)

        14

SQL> commit;
Commit complete.

since we've committed, we'll see the session based rows but not the transaction based rows:

SQL> SQL> select count(*) from temp_table_session   2 /

  COUNT(*)

        14

SQL> select count(*) from temp_table_transaction   2 /

  COUNT(*)

         0

SQL> SQL> connect tkyte/tkyte
Connected.
SQL> since we've started a new session, we'll see no rows now:

SQL> SQL> select count(*) from temp_table_session   2 /

  COUNT(*)

         0

SQL> select count(*) from temp_table_transaction   2 /

  COUNT(*)

         0

SQL> Instead of executing "select x, y, z into #temp from some_table" you would:

o once per database create "TEMP" as a global temporary table.

o then in your procedures you would simply "insert into temp (x,y,z) select x,y,y from some_table"

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp