Using commit, PL-SQL Programming


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.



UPDATE accts SET bal = my_bal - debit

WHERE acctno = 7715;


UPDATE accts SET bal = my_bal + credit

WHERE acctno = 7720;



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
Role of Abstraction in pl/sql: The abstraction is a high-level description or model of a real-world entity. The Abstractions keep our daily lives convenient. They help us ca

How Calls Are Resolved? The figure shows that how the PL/SQL compiler resolves the subprogram calls. When the compiler encounters the procedure or function call, it tries to di

GOTO Statement   The GOTO statement branches categorically to a block label or statement label. The label should be exclusive within its scope and should precede a PL/SQL bloc

Natural Join - SQL In the absence of NATURAL JOIN Example has to be replaced by something rather more longwinded, as shown in Example. Example: Joining IS_CALLED and IS_EN

Using EXTEND To enlarge the size of a collection, use EXTEND. This process has 3 forms. The EXTEND appends one null element to a collection. And the EXTEND(n) appends n null e

NULL Statement The NULL statement clearly specifies in action; it does nothing other than to pass control to the next statement. It can, though, improve the readability. In a

What is Cursors how to use it in Real time application ?

Manipulating Individual Elements Faraway you have manipulated an entire collection. Within the SQL, to manipulate the individual elements of the collection, and then use the ope

Data Abstraction The Data abstraction extracts the important properties of data while ignoring the not necessary details. Once you design a data structure, you can fail to reme

What Is a Collection The collection is an ordered group of elements, all of similar type (for e.g. the grades for a class of students). Each element has a unique subscript whic