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
Existential Quantification - SQL Existential quantification-stating that something is true of at least one object under consideration-can be expressed by OR(r,c), meaning tha

Albeit simple method : These all the truth tables give us our first as albeit simple method for proving a theorem: where check whether it can be written in propositional logic

We are seeking a freelance consultant that is familiar with Appgen applications. We require exporting all our data into a format appropriate for importing into SAP Business One. Pl

Keyword and Parameter Description: boolean_expression: This is an expression which results the Boolean value TRUE, FALSE, & NULL. It is related with a series of statement

Updating by insertion Syntax : INSERT INTO ENROLMENT VALUES (SID ('S4'), 'Devinder', CID ('C1'));

%FOUND Subsequent to a cursor or cursor variable is opened but before the first fetch, the %FOUND yields NULL. Afterward, it yields TRUE when the last fetch returned a row, or

PITS Depressions in secondary cell wall is called pit. A pit present on the free cell wall surface without its partner is called Blind pit. It consists of 2 parts -

Collection Methods:   The collection method is a built-in function or procedure which operates on the collections and is called using the dot notation. The methods like the C

%ROWCOUNT The %ROWCOUNT yields the number of rows affected by the INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement. The %ROWCOUNT yields zero when a

Ending Transactions A good quality programming practice is to commit or roll back every transaction explicitly. Whether you rollback or issue the commit in your PL/SQL program