What’s obvious from reading the Oracle Concepts and PL\/SQL Developers Guide is that Oracle sets an implicit savepoint before each single SQL statement and in case the statement fails (for whatever reason) Oracle rolls back to that savepoint. That’s what’s called “statement level rollback”. Apparently, for stored procedures that are called from a client the same rules apply. Before the procedure call a savepoint is set and if that procedure raises\/propagates an exception Oracle rolls back to the implicit savepoint set just before the procedure call. It is fundamental to understand this behaviour as a Oracle database developer, especially when it comes to exception handling. Let’s examine the implications. First we create the table with index and constraint:<\/p>\n
\r\nSQL> create table t1 (id number, text varchar2(50));\r\nTable created.\r\n\r\nSQL> create unique index t1_idx_01 on t1 (text);\r\nIndex created.\r\n\r\nSQL> alter table t1 add constraint t1_uk_01 unique (text) using index t1_idx_01;\r\nTable altered.\r\n<\/pre>\nThen a stored procedure to insert some data into table T1:<\/p>\n
\r\nSQL> create or replace procedure insert_t1\r\nis\r\nbegin\r\n dbms_output.put_line('inserting first row');\r\n insert into t1 (id, text) values (1, 'first');\r\n\r\n dbms_output.put_line('inserting first row');\r\n insert into t1 (id, text) values (2, 'first'); -- this will rais ORA-00001\r\n\r\n dbms_output.put_line('inserting third row');\r\n insert into t1 (id, text) values (3, 'third'); -- this statement will not be executed due to previous exception\r\nend insert_t1;\r\n\/\r\nProcedure created.\r\n<\/pre>\nThis procedure tries to insert three records into table T1 but due to the unique constraint on TEXT the second insert statement will fail and the procedure returns with an exception propagated to the caller and subsequently everything done within the procedure will be rolled back.
\nLet’s put that to a test:<\/p>\n\r\nSQL> truncate table t1;\r\nTable truncated.\r\n\r\nSQL> exec insert_t1\r\nBEGIN insert_t1; END;\r\n*\r\nERROR at line 1:\r\nORA-00001: unique constraint (TESTUSER.T1_UK_01) violated\r\nORA-06512: at "TESTUSER.INSERT_T1", line 8\r\nORA-06512: at line 1\r\n\r\nSQL> select * from t1;\r\nno rows selected\r\n<\/pre>\nSo far so good, now let’s see what happens when we trap ORA-00001 (PL\/SQL named exception DUP_VAL_ON_INDEX) and swallow it, not propagating the exception to the top-level call.<\/p>\n
\r\nSQL> create or replace procedure insert_t1\r\nis\r\nbegin\r\n dbms_output.put_line('inserting first row');\r\n insert into t1 (id, text) values (1, 'first');\r\n\r\n dbms_output.put_line('inserting first row');\r\n insert into t1 (id, text) values (2, 'first'); -- this will rais ORA-00001\r\n\r\n dbms_output.put_line('inserting third row');\r\n insert into t1 (id, text) values (3, 'third'); -- this statement will not be executed due to previous exception\r\nexception\r\nwhen dup_val_on_index then\r\n null;\r\nend insert_t1;\r\n\/\r\nProcedure created.\r\n<\/pre>\nThis time the procedures again tries to insert three records into table T1 but fails badly with the second record. The exception is trapped and silently swallowed so the procedure returns without an error, no exception propagation and therefore leaving one record in the table.<\/p>\n
\r\nSQL> truncate table t1;\r\nTable truncated.\r\n\r\nSQL> exec insert_t1\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> select * from t1;\r\nID TEXT\r\n---------- --------------------------------------------------\r\n1 first\r\n\r\n1 row selected.\r\n<\/pre>\nOoops, something went wrong and we didn’t notice, if we proceed with the program flow in the application and eventually commit the transaction we leave the database in a inconsistent state as we expect three records to be present and not only one.<\/p>\n
Alright, next time just let’s log the error and propagate it:<\/p>\n
\r\nSQL> create or replace procedure insert_t1\r\nis\r\nbegin\r\n dbms_output.put_line('inserting first row');\r\n insert into t1 (id, text) values (1, 'first');\r\n\r\n dbms_output.put_line('inserting first row');\r\n insert into t1 (id, text) values (2, 'first'); -- this will rais ORA-00001\r\n\r\n dbms_output.put_line('inserting third row');\r\n insert into t1 (id, text) values (3, 'third'); -- this statement will not be executed due to previous exception\r\nexception\r\nwhen dup_val_on_index then\r\n dbms_output.put_line('Error: duplicate records');\r\n raise_application_error(-20001, 'There can only be one "first"!');\r\nend insert_t1;\r\n\/\r\nProcedure created.\r\n<\/pre>\nThere we go, again whatever the application does we ensure the consistency of the data within the database.<\/p>\n
\r\nSQL> truncate table t1;\r\nTable truncated.\r\n\r\nSQL> exec insert_t1\r\nBEGIN insert_t1; END;\r\n*\r\nERROR at line 1:\r\nORA-20001: There can only be one "first"!\r\nORA-06512: at "TESTUSER.INSERT_T1", line 15\r\nORA-06512: at line 1\r\n\r\nSQL> select * from t1;\r\nno rows selected\r\n<\/pre>\nThe propagation of exceptions can also be observed in a SQL trace where for a cursor “begin insert_t1; end;” with depth 0 a ERROR is following the EXEC call. If the error is trapped and ignored there is only a ERROR following the EXEC call of the the cursor “INSERT INTO T1(ID, TEXT) VALUES (2, ‘first’)” with depth 1 and no ERROR for the parent cursor.<\/p>\n
Conclusion:
\nBe careful with stored procedures that trap exceptions and do not propagate them as this kind of breaks the statement level rollback that Oracle would perform when the exceptions are not caught at all or are being re-raised.<\/p>\nFoot note 1: in this text “stored procedure” or “procedure” represents any named PL\/SQL construct (except triggers)<\/p>\n","protected":false},"excerpt":{"rendered":"
What’s obvious from reading the Oracle Concepts and PL\/SQL Developers Guide is that Oracle sets an implicit savepoint before each single SQL statement and in case the statement fails (for whatever reason) Oracle rolls back to that savepoint. That’s what’s called “statement level rollback”. Apparently, for stored procedures that are called from a client the […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-13","post","type-post","status-publish","format-standard","hentry","category-internals"],"_links":{"self":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/13","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=13"}],"version-history":[{"count":7,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/13\/revisions"}],"predecessor-version":[{"id":25,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=\/wp\/v2\/posts\/13\/revisions\/25"}],"wp:attachment":[{"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=13"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=13"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.spotonoracle.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=13"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}