Using EXECUTE IMMEDIATE inside PL\SQL Block

Question

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.

• user1822: Any DDL statement will commit a pending transaction regardless whether the DDL is successful or not. Oracle does not support transactional DDL.
• user1822: Cross post: stackoverflow.com/questions/23803601/…

Answer 1

In Oracle when you create a table or execute an alter statement there is an implied commit. You may want to create the table in a separate function returning a boolean value with "PRAGMA AUTONOMOUS_TRANSACTION" then the calling procedure would get a true/false response as to whether or not the table was created. you will then be able to commit or rollback your insert.

• Justin Cave: Any DDL statement causes an implicit commit. Not every ALTER statement, however, is DDL so not every ALTER statement does an implicit commit. For example, ALTER SESSION SET nls_date_format = ... is an ALTER statement that is not DDL and that does not implicitly commit.
• Gandolf989: Right it needs to be DDL, as in alter table, alter index, etc.