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
Controlling Cursor Variables You use 3 statements to control the cursor variable: OPEN-FOR, FETCH, & CLOSE. At First, you OPEN a cursor variable FOR a multi-row query. Then, y

Using Cursor Attributes: Every cursor has 4 attributes: %NOTFOUND, %FOUND, %ISOPEN, and %ROWCOUNT. If appended to the cursor name, they return the helpful information about

Data Types in SQL SQL's concept does not differ significantly from that defined in the theory book, apart from that business concerning NULL. However, the theory book equates

Effects of NULL The numeric variable X, perhaps of type INTEGER, might be assigned NULL. In that case the result of evaluating X + 1 is NULL, and so SET Y = X + 1 assigns NULL

THEO R Y OF PANSPERMIA - Arrhenius (1908) postulated the cosmic panspermia theory that claims that organisms existed throughout the universe, and their spores, etc., could

Question: (a) The objective of query optimization is to choose the most efficient strategy for implementing a given relational query, thereby improving the system performance. On

Predicate - SQL Consider the declarative sentence-a proposition-that is used to introduce this topic:  "Student S1, named Anne, is enrolled on course C1." Recall that th

Complete the following steps to create a procedure to calculate the tax on an order. The BB_TAX table contains the states that require taxes to be submitted for Internet sales. If

Use Object Types and Collections The Collection types and object types increase your efficiency by allowing for the realistic data modeling. The Complex real-world entities an

Entering and Exiting If you enter the executable part of an autonomous routine, the major transaction suspends. When you exit the routine, the major transaction resumes. To ex