Re: Transactional DDL

On 6/2/07, Jasbinder Singh Bali <jsbali(at)gmail(dot)com> wrote:
> But its said that transactions in any RDBMS follow ACID properties.
> So if i put a create table and an Insert statement in the same begin end
> block as one single transactioin, won't both create and insert follow acid
> property, being in one single trasaction, and either both get committed or
> none, talking about oracle lets say

Actually, Oracle inserts implicit COMMIT after each DDL.

So, if you have:

BEGIN;
INSERT INTO foo (bar) VALUES (1);
CREATE INDEX foo_bar ON foo (bar);
-- Here Oracle will insert implicit COMMIT, thus your foo table will
have value 1 commited.
-- And here Oracle will BEGIN a new trasaction.
INSERT INTO foo (bar) VALUES (2);
ROLLBACK;
-- And you will rollback the insert of value 2. Value 1 remains in the table,
-- because it is already committed.

Regards,
Dawid