Implicit commit caused by DDL in Oracle PL/SQL

1. If the DDL syntax is correct, implicit commit will occur even if the execution fails:

Duzz $ Scott @ orcl> Create Table T1 as select * from Dept;

 

Table created.

 

Elapsed: 00:00:00. 03

Duzz $ Scott @ orcl> Update T1 set loc = 'xx' where deptno = 10;

 

1 row updated.

 

Elapsed: 00:00:00. 03

Duzz $ Scott @ orcl> drop table xx;

Drop table xx

*

Error at line 1:

ORA-00942: Table or view does not exist

 

 

Elapsed: 00:00:00. 01

 

++ ++

 

Duzz $ sys @ orcl> select * from Scott. T1;

 

Deptno dname Loc

-------------------------------------------------------------------------------------------

10 Accounting xx

20 research Dallas

30 sales Chicago

40 operations Boston

 

Elapsed: 00:00:00. 01

 

 

2. If the DDL syntax is incorrect, it will not cause implicit commit or automatic rollback of transactions:

 

Duzz $ Scott @ orcl> Update T1 set loc = 'yy' where deptno = 20;

 

1 row updated.

 

Elapsed: 00:00:00. 00

Duzz $ Scott @ orcl> drop table Col xx;

Drop table Col xx

*

Error at line 1:

ORA-00933: SQL command not properly ended

 

 

Elapsed: 00:00:00. 00

 

++ ++

 

Duzz $ sys @ orcl> select * from Scott. T1;

 

Deptno dname Loc

-------------------------------------------------------------------------------------------

10 Accounting xx

20 research Dallas

30 sales Chicago

40 operations Boston

 

Elapsed: 00:00:00. 00

 

Summary:

DDL pseudocode:

Begin

Parse DDL;

Commit;

Do DDL;

Exception

When others then

NULL;

End;

 

Ref:

Http://www.cublog.cn/u/25770/showart_2222436.html