Use savepoint before each function begins - Oracle Transaction

Q.  Can we use savepoint before each function begins?
- Published on 12 Aug 15

a. Yes
b. No

ANSWER: Yes
 

    Discussion

  • Nirja Shah   -Posted on 22 Sep 15
    - You can declare intermediate markers called savepoints within the context of a transaction.

    - Savepoints divide a long transaction into smaller parts.

    - Using savepoints, you can arbitrarily mark your work at any point within a long transaction.

    - You then have the option later of rolling back work performed before the current point in the transaction but after a declared savepoint within the transaction.

    - For example, you can use savepoints throughout a long complex series of updates, so if you make an error, you do not need to resubmit every statement.

    - Savepoints are similarly useful in application programs.

    - If a procedure contains several functions, then you can create a savepoint before each function begins.

    - Then, if a function fails, it is easy to return the data to its state before the function began and re-run the function with revised parameters or perform a recovery action.

    - After a rollback to a savepoint, Oracle releases the data locks obtained by rolled back statements.

    - Other transactions that were waiting for the previously locked resources can proceed.

    - Other transactions that want to update previously locked rows can do so.

Post your comment / Share knowledge


Enter the code shown above:
 
(Note: If you cannot read the numbers in the above image, reload the page to generate a new one.)