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
Using PRIOR and NEXT The PRIOR(n) returns the index number that precede index n in a collection. The NEXT(n) returns the index number which succeed the index n. If n has no pr

Initializing Objects: Till you initialize an object by calling the constructor for its object type, the object is automatically null. That is, the object itself is null, not me

Type versus Representation Confusion in SQL This describes how a value might have two or more distinct representations. For example, user-defined type POINT might have a decla

TTITLE and BTITLE are commands in Pl-SQL to control report headings and footers. This Ttitle & Btitle are mainly used on creating SQL*PLUS report. Ttitle is used for toptitle headi

Positional Notation The first procedure call uses the positional notation. The PL/SQL compiler relates the first actual parameter, account, with the first proper parameter, ac

Scope and Visibility The References to an identifier are resolved according to its visibility and scope. The scope of an identifier is that area of a program unit (subprogram, b

Project Description: I want a small relational database to be built. I want the database to have the subsequent information tables: Employee Information Document storage

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

UTL_FILE: The Package UTL_FILE permits your PL/SQL programs to read & write operating system (OS) text files. It gives a restricted version of the standard OS stream file I/O,

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