Using set transaction, PL-SQL Programming


You use the SET TRANSACTION statement to begin the read-only or read-write transaction, start an isolation level, or assign your present transaction to a specified rollback segment. The Read-only transactions are helpful for running the multiple queries against one or more tables whereas other users update the same tables.

During the read-only transaction, all the queries refer to similar snapshot of the database, providing the multi-query, multi-table, read-consistent view. The Other users can carry on to query or update data as usual. The commit or rollback ends the transaction. In the illustration below, like the store manager, you use the read-only transaction to meet the sales figures for the day, the past week & the past month.

The figures are unaffected by the other users updating the database throughout the transaction.


daily_sales REAL;

weekly_sales REAL;

monthly_sales REAL;



COMMIT; -- ends previous transaction


SELECT SUM(amt) INTO daily_sales FROM sales


SELECT SUM(amt) INTO weekly_sales FROM sales

WHERE dte > SYSDATE - 7;

SELECT SUM(amt) INTO monthly_sales FROM sales

WHERE dte > SYSDATE - 30;

COMMIT; -- ends read-only transaction



The SET TRANSACTION statement should be the first SQL statement in the read-only transaction and can only come out once in the transaction. When you set a transaction to the READ ONLY, the following queries see only changes committed before the transaction began. The use of READ ONLY does not affect the other users or transactions.

Posted Date: 10/4/2012 5:19:36 AM | Location : United States

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

Write discussion on Using set transaction
Your posts are moderated
Related Questions
Consider the schema for FreeChecking Bank, that we designed given below. Translate the given ER schema into SQL CREATE TABLE statements (indicating primary key, unique and foreign

Develop Data Business Intelligence Project Project Description: We are linking our Microsoft SQL Database to GoodData Business Intelligence. We are seeking somebody who has e

Project Description: I am looking to change FullCalendar to add/delete sql server data when events are removed or dropped from Calendar. Events should only be included by dra

Ensuring Backward Compatibility   The PL/SQL Version 2 permits some abnormal behavior which Version 8 disallows. Particularly, Version 2 permits you to (i) Make the forw

Product-specific Packages The Oracle and different Oracle tools are supplied with the product-specific packages which help you to build the PL/SQL-based applications. For illus

Using LOCK TABLE You use the LOCK TABLE statement to lock the whole database tables in the specified lock mode so that you can share or deny the access to them. For illustrati

Recursion versus Iteration Dissimilar the iteration, recursion is not crucial to PL/SQL programming. Any problem which can be solved using recursion can be solving using the it

EXIT The EXIT statement forces a loop to done unconditionally. Whenever an EXIT statement is encountered, the loop is done immediately and controls the passes to the next statem

Using ROLLBACK The ROLLBACK statements end the present transaction and undo any change made during the transaction. The Rolling back is helpful for two reasons. Firstly, if yo

Task 2 [12 marks] Write the package body for the following package specification (the detailed description of each function and procedure is provided in the appendix below). Place