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.

This is because DDL is not transactionnal in Oracle. See: [Oracle: DDL and transaction rollback](http://stackoverflow.com/questions/4711447/oracle-ddl-and-transaction-rollback). `EXECUTE IMMEDIATE` doesn't commit, but your `CREATE TABLE` does. — Vincent Malgrat, May 22 '14 at 10:09

score 3 · Accepted Answer · answered May 22 '14 at 11:00

You can ty this PL/SQL Code:

begin 

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:'); 

INSERT INTO Customer ( GUID, STATUS, NAME) VALUES (1,1,'xx');

EXCEPTION  -- exception handlers begin 
    WHEN OTHERS THEN  -- handles all other errors 
    DBMS_OUTPUT.PUT_LINE('Error occured, rollback...'); 

ROLLBACK;
end;

So firstly it will create the table, if exception occurs it will rollback the transaction and insert statement will not work. Hope this will work for you..

Using EXECUTE IMMEDIATE inside PL\SQL Block

1 Answers1