Using EXECUTE IMMEDIATE inside PL\SQL block makes the whole block commit immediately.
begin INSERT INTO Customer ( GUID, STATUS, NAME) VALUES (1,1,'xx'); EXECUTE IMMEDIATE 'CREATE TABLE Shop ( GUID NUMBER(16), STATUS NUMBER(1), NAME VARCHAR2(50 BYTE), ) LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING'; DBMS_OUTPUT.PUT_LINE('DONE:'); EXCEPTION -- exception handlers begin WHEN OTHERS THEN -- handles all other errors DBMS_OUTPUT.PUT_LINE('Error occured, rollback...'); ROLLBACK; end;
As you realized I do not even use COMMIT. About above code,
"Insert into" statement works, but "create table" statement throws exception because there is already a table with the same name in the database.
Both I did not have any commit statement and code block fell exception and rolled back when I looked at the database I saw that insert had worked and there was a new row. It was expected that it should not have been there because there is no commit and also rollback worked..
How can I make rollback when exception occurs.