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
Data Types in SQL SQL's concept does not differ significantly from that defined in the theory book, apart from that business concerning NULL. However, the theory book equates

MECHANISTI S THEORY-HAECKEL (1866) - Haeckel stating that after each catalysm, some new organism suddenly forms as a chance event in one stride from inanimate matter and sub

COMMIT Statement The COMMIT statement explicitly makes everlasting changes to the database during the present transaction. The Changes made to the database are not considered e

Declaring Cursor Variables Once a REF CURSOR type is define by you, and then you can declare the cursor variables of that type in any PL/SQL block or subprogram. In the exampl

Declaring Exceptions The Exceptions can be declared only in the declarative part of the PL/SQL subprogram, block, or package. By introducing its name, you can declare an excep

Example of WRAP Operator - SQL The effect of Example can be obtained in SQL but note that one needs to write down not only the names of the columns being wrapped but also the

Records Records are the items of the type RECORD. The Records have exclusively named fields that can store the data values of various types. And hence, a record treat associate

Parameter and Keyword Description: procedure_name The user-defined procedure is declared by this construct. parameter_name: This identifies the formal parameter t

Closing a Cursor The CLOSE statements disable the cursor, and the result set becomes undefined. An illustration of the CLOSE statement as shown: CLOSE c1;

Tautologies: Above given table allows us to read the truth of the connectives in the next manner. Just expect we are looking at row three. It means this says that, if there P