Using commit, PL-SQL Programming

Using COMMIT

The COMMIT statements end the present transaction and make permanent any changes made during that transaction. Till you commit the changes, other users cannot access the changed data; they see the data as it was before you made the changes.

Consider a simple transaction which transfers money from one bank account to the other. The transaction needs two updates as it debits the first account, and then credits the second. In the illustration below, after crediting the second account, you issue a commit that makes the changes everlasting. Only then do other users see the changes.

BEGIN

...

UPDATE accts SET bal = my_bal - debit

WHERE acctno = 7715;

...

UPDATE accts SET bal = my_bal + credit

WHERE acctno = 7720;

COMMIT WORK;

END;

The COMMIT statements release all row and table locks. It also erases any savepoint marked as the last commit or rollback. The elective keyword WORK has no effect other than to get better readability. The keyword END signals the end of the PL/SQL block, not the end of the transaction. Now as a block can span a multiple transactions, the transaction can cover multiple blocks.

The COMMENT clause specifies a comment to be related with the distributed transaction. If you issue a commit, the changes to each database affected by a distributed transaction are made permanent. Though, if a network or machine fails during a commit, the state of the distributed transaction may be unknown or in doubt. In that situation, the Oracle stores the text specified by the COMMENT in the data dictionary beside with the transaction ID. The text should be a quoted literal up to 50 characters in size.  

Posted Date: 10/4/2012 5:13:40 AM | Location : United States







Related Discussions:- Using commit, Assignment Help, Ask Question on Using commit, Get Answer, Expert's Help, Using commit Discussions

Write discussion on Using commit
Your posts are moderated
Related Questions
Naming Conventions The similar naming conventions apply to all PL/SQL program items and units including the variables, cursors, constants, cursor variables, procedures, exception

Parameter and Keyword Description: select_item: This select_item is a value returned by the SELECT statement, and then assigned to the equivalent variable or field in the

Main features of PL/SQL A good way to get familiar with PL/SQL is to look at a sample program. The below program processes an order for tennis rackets. At first, it declares a

Using Cursor Attributes To process the SQL data manipulation statements, the SQL engine must opens an implicit cursor named SQL. This cursor's attributes (%FOUND, %NOTFOUND, %

Data Types in SQL - Integer INTEGER or  synonymously INT, for integers within a certain range. SQL additionally has types SMALLINT and BIGINT for certain ranges of integers. T

Example of GROUP BY and COLLECT Operator Example: Using GROUP BY and COLLECT to obtain C_ER2 SELECT CourseId, CAST ( COLLECT (ROW (StudentId, Mark)) AS ROW (Studen

DECLARE : This keyword signals the beginning of the declarative section of the PL/SQL block, that contains local declarations. The Items declared locally exist only within the

Using EXISTS The EXISTS(n) returns TRUE if the nth element in a collection exist. Or else, EXISTS(n) returns FALSE. Primarily, you use EXISTS with DELETE to maintain the spars

MECHANISTI S THEORY-HAECKEL (1866) - Haeckel stating that after each catalysm, some new organism suddenly forms as a chance event in one stride from inanimate matter and sub

Positional Notation The first procedure call uses the positional notation. The PL/SQL compiler relates the first actual parameter, account, with the first proper parameter, ac