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.