Re: CREATE SEQUENCE commits transaction

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1 Mar 2002 06:44:23 -0800
Message-ID: <a5o44701136@drn.newsguy.com>

In article <3C7F63D4.D96A40B0_at_dimedis.de>, Gert says...
>
>
>Hello,
>
>i have found that the CREATE SEQUENCE statement commits the current
>transaction. Is this behaviour correct?
>
>I understand that a sequence is not handled inside a transaction to
>avoid lockings, but it should not commit a running transaction, isn't
>it!?
>
>Gert

As the others have said -- DDL commits. Conceptually, DDL can be thought to be processed as follows:

begin

    commit;
    begin

         do the DDL;   
         commit;
    exception 
         when others then
              rollback;

    end;
end;
/

If you don't want DDL to commit YOUR transaction and your using 8i and up, you can use autonomous transactions:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> drop sequence my_seq   2 /

Sequence dropped.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> drop table t;

Table dropped.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t ( x int );

Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t values ( 1 );

1 row created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> declare   2 pragma autonomous_transaction;   3 begin
  4 execute immediate 'create sequence my_seq';   5 end;
  6 /

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select * from t;

         X

---

         1

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> rollback;

Rollback complete.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select * from t;

no rows selected

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select object_name, object_type from user_objects where object_name = 'MY_SEQ';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------
MY_SEQ                         SEQUENCE


    

--
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 Received on Fri Mar 01 2002 - 08:44:23 CST