Skip to Main Content

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Auto commit DDL: Difference between SQL developer and SQL*Plus?

620159Nov 10 2009 — edited Dec 10 2009
I asked this in the SQL forum (986252), but it was suggested there that it might be an SQL developer issue, so I thought I'd ask here.

I am trying to understand what exactly happens when an error occurs when a transaction gets committed because of a DDL statement. I noticed that in SQL Developer, if the DDL statement is CREATE TABLE, the statement gets executed, even if the DML statements that I entered before the CREATE TABLE violate a deferred constraint, so that the transaction fails. In SQL*Plus this is not the case: the table is not created. I do not have auto-commit on. I use Oracle 10g XE.

This is what happens in SQL*Plus:
SQL> create table table1(a number);

Table created.

SQL> alter table table1 add constraint table1_a_uq unique(a) deferrable initially deferred;

Table altered.

SQL> insert into table1 values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into table1 values(1);

1 row created.

SQL> create table table2(b number);
create table table2(b number)
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (BOEK.TABLE1_A_UQ) violated


SQL> select * from table2;
select * from table2
              *
ERROR at line 1:
ORA-00942: table or view does not exist
If I put the exact same code in SQL Developer (both versions 1.5.5 and 2.1.0.63) The table gets created.
This is my script:
create table table1(a number);
alter table table1 add constraint table1_a_uq unique(a) deferrable initially deferred;
insert into table1 values(1);
commit;
insert into table1 values(1);
create table table2(b number);
select * from table2;
And this is the SQL Developer output:
create table succeeded.
 alter table table1 succeeded.
1 rows inserted
commited
1 rows inserted

Error starting at line 9 in command:
create table table2(b number)
Error report:
SQL Error: ORA-02091: transaction rolled back
ORA-00001: unique constraint (BOEK.TABLE1_A_UQ) violated
02091. 00000 -  "transaction rolled back"
*Cause:    Also see error 2092. If the transaction is aborted at a remote
           site then you will only see 2091; if aborted at host then you will
           see 2092 and 2091.
*Action:   Add rollback segment and retry the transaction.
B                      
---------------------- 

0 rows selected
As you can see, the table gets created in SQL Developer, but not in SQL*Plus.

I wonder why this is different.
This post has been answered by -K- on Nov 11 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 7 2010
Added on Nov 10 2009
5 comments
3,874 views