1

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.

2

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.

  • 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. – Justin Cave May 22 '14 at 18:14
  • Right it needs to be DDL, as in alter table, alter index, etc. – Gandolf989 May 22 '14 at 18:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.