BEGIN - END block atomic transactions in PL SQL

Question

This information should be easy to find  but I haven t had any luck   When I have a BEGIN - END block in a PL SQL  does it behave as an atomic transaction  that will try to commit on hitting the END block and if anything goes wrong rolls back the changes   If not  how do I make sure that the code inside the BEGIN - END block behaves like an atomic transaction and how does the block behave  by default    EDIT  I am running from a stored procedure and I am using an implicit block  I think

User · Answer

You don t mention if this is an anonymous PL SQL block or a declarative one ie  Package  Procedure or Function  However  in PL SQL a COMMIT must be explicitly made to save your transaction s  to the database  The COMMIT actually saves all unsaved transactions to the database from your current user s session   If an error occurs the transaction implicitly does a ROLLBACK   This is the default behaviour for PL SQL

User · Answer

BEGIN-END blocks are the building blocks of PL SQL  and each PL SQL unit is contained within at least one such block  Nesting BEGIN-END blocks within PL SQL blocks is usually done to trap certain exceptions and handle that special exception and then raise unrelated exceptions  Nevertheless  in PL SQL you  the client  must always issue a commit or rollback for the transaction   If you wish to have atomic transactions within a PL SQL containing transaction  you need to declare a PRAGMA AUTONOMOUS TRANSACTION in the declaration block  This will ensure that any DML within that block can be committed or rolledback independently of the containing transaction   However  you cannot declare this pragma for nested blocks  You can only declare this for    Top-level  not nested  anonymous PL SQL blocks  List item Local  standalone  and packaged functions and procedures  Methods of a SQL object type Database triggers   Reference  Oracle

User · Answer

The default behavior of Commit PL SQL block   You should explicitly commit or roll back every transaction  Whether you issue the commit or rollback in your PL SQL program or from a client program depends on the application logic  If you do not commit or roll back a transaction explicitly  the client environment determines its final state  For example  in the SQLPlus environment  if your PL SQL block does not include a COMMIT or ROLLBACK statement  the final state of your transaction depends on what you do after running the block  If you execute a data definition  data control  or COMMIT statement or if you issue the EXIT  DISCONNECT  or QUIT command  Oracle commits the transaction  If you execute a ROLLBACK statement or abort the SQLPlus session  Oracle rolls back the transaction   https   docs oracle com cd B19306 01 appdev 102 b14261 sqloperations htm i7105

User · Answer

Firstly  BEGIN  END are merely syntactic elements  and have nothing to do with transactions   Secondly  in Oracle all individual DML statements are atomic  i e  they either succeed in full  or rollback any intermediate changes on the first failure   unless you use the EXCEPTIONS INTO option  which I won t go into here    If you wish a group of statements to be treated as a single atomic transaction  you d do something like this   BEGIN   SAVEPOINT start tran    INSERT INTO        -- first DML   UPDATE        -- second DML   BEGIN     END  -- some other work   UPDATE        -- final DML EXCEPTION   WHEN OTHERS THEN     ROLLBACK TO start tran      RAISE  END    That way  any exception will cause the statements in this block to be rolled back  but any statements that were run prior to this block will not be rolled back   Note that I don t include a COMMIT - usually I prefer the calling process to issue the commit     It is true that a BEGIN  END block with no exception handler will automatically handle this for you   BEGIN   INSERT INTO        -- first DML   UPDATE        -- second DML   BEGIN     END  -- some other work   UPDATE        -- final DML END    If an exception is raised  all the inserts and updates will be rolled back  but as soon as you want to add an exception handler  it won t rollback  So I prefer the explicit method using savepoints

[oracle] BEGIN - END block atomic transactions in PL/SQL

Examples related to oracle

Examples related to plsql