Fetching across commits, PL-SQL Programming

Fetching Across Commits

The FOR UPDATE clauses acquire exclusive all row locks. All rows are locked when you open the cursor, and when you commit your transaction they are unlocked. Therefore, you cannot fetch from a FOR UPDATE cursor after a commit. If you do, the PL/SQL raises an exception. In the illustration below, the cursor FOR loop fails after the tenth insert:

DECLARE

CURSOR c1 IS SELECT ename FROM emp FOR UPDATE OF sal;

ctr NUMBER := 0;

BEGIN

FOR emp_rec IN c1 LOOP -- FETCHes implicitly

...

ctr := ctr + 1;

INSERT INTO temp VALUES (ctr, 'still going');

IF ctr >= 10 THEN

COMMIT; -- releases locks

END IF;

END LOOP;

END;

If you want to fetch across the commits, do not use the FOR UPDATE and CURRENT OF clauses. Rather, use the ROWID pseudocolumn to mimic the CURRENT OF clause.

Merely select the rowid of each row into a UROWID variable. Then, use the rowid to identify the present row during the subsequent updates and deletes. An illustration is as shown:

DECLARE

CURSOR c1 IS SELECT ename, job, rowid FROM emp;

my_ename emp.ename%TYPE;

my_job emp.job%TYPE;

my_rowid UROWID;

BEGIN

OPEN c1;

LOOP

FETCH c1 INTO my_ename, my_job, my_rowid;

EXIT WHEN c1%NOTFOUND;

UPDATE emp SET sal = sal * 1.05 WHERE rowid = my_rowid;

-- this mimics WHERE CURRENT OF c1

COMMIT;

END LOOP;

CLOSE c1;

END;

Posted Date: 10/4/2012 5:21:42 AM | Location : United States







Related Discussions:- Fetching across commits, Assignment Help, Ask Question on Fetching across commits, Get Answer, Expert's Help, Fetching across commits Discussions

Write discussion on Fetching across commits
Your posts are moderated
Related Questions
Many of the reports generated from the system calculate the total dollars in purchases for a shopper. Complete the following steps to create a function named TOT_PURCH_SF that acce

Effects of NULL in Table Literal When a VALUES expression appears as the source value for an SQL INSERT statement, the key word NULL can appear as a field value, such that for

Aggregate Assignment The %ROWTYPE declaration cannot include an initialization clause. Though, there are two ways to assign values to all fields in a record at once. At First, t

Object Type: The object type is a user-defined composite datatype which encapsulates a data structure along with the functions and procedures required to manipulate the data

CHECK Constraints in SQL A CHECK constraint is a table constraint defined using the key word CHECK, as already illustrated in several examples in this chapter. In particular,

Using DEFAULT You can use the keyword DEFAULT rather than that of the assignment operator to initialize the variables. For e.g. the declaration blood_type CHAR := ’O’; it can b

Logical Connectives - SQL SQL's extended truth tables in which the symbol, for unknown, appears along with the usual T and F. Negation (NOT, ¬) Conjunction (

I need SQL Data Base Project Description: Network SQL database and SQL Setup two 4 workstation Skills required are Data Entry, MySQL, SQL

Definition of FROM - SQL Recall that the operand of FROM is denoted by a commalist, each element of that commalist being a table expression optionally accompanied by a range v

Predefined Exceptions The internal exception is raised implicitly whenever your PL/SQL program exceeds a system-dependent limit or violates an Oracle rule. Each & every Oracle