Using commit, PL-SQL Programming

Assignment Help:

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.  


Related Discussions:- Using commit

Cursor variables as parameters, Cursor Variables As Parameters You can...

Cursor Variables As Parameters You can declare the cursor variables as the formal parameters of the functions and procedures. In the illustration below, you define the REF CUR

Write sql queries, Write SQL queries to solve the following specifications....

Write SQL queries to solve the following specifications. Include the query AND THE OUTPUT.  A screen dump of the output is acceptable. Show as many rows as you can. A screen dump i

Using commit, Using COMMIT The COMMIT statements end the present trans...

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 acc

Using operator ref - manipulating objects pl sql, Using Operator REF: ...

Using Operator REF: You can retrieve refs by using the operator REF that, like VALUE, takes as its argument a correlation variable. In the illustration below, you retrieve one

Pl/sql conditional control: if statements, Pl/sql Conditional Control: IF s...

Pl/sql Conditional Control: IF statements Frequently, it is necessary to take the alternative actions depending on the circumstances. The IF statement execute a series of statem

Difference between 9i & 10g, Difference between 9i & 10G When Oracle r...

Difference between 9i & 10G When Oracle releases any new databases then it are having some discrepancy with them. But 10G is having much difference than oracle 9i has. Oracle

Role of abstraction in pl/sql, Role of Abstraction in pl/sql: The abst...

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

Cartesian product, Using a Join on 2 tables, select all columns and all row...

Using a Join on 2 tables, select all columns and all rows from the tables without the use of a Cartesian product. Query: SELECT * FROM EMPLOYEE1 JOIN CONTRACT ON EMPLOYEE

Parameter and keyword description - update statement, Parameter and Keyword...

Parameter and Keyword Description:   table_reference: This keyword identifies the table or view that should be accessible when you execute the UPDATE statement, and for wh

Parameter and keyword description - loop statements, Parameter and Keyword ...

Parameter and Keyword Description: label_name: This is an undeclared identifier which optionally labels a loop. When used, the label_name should be enclosed by double ang

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd