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.
Best Answer
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.