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
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

FORALL Statement The FORALL statements instruct the PL/SQL engine to bulk-bind the input collections before sending them to the SQL engine. Though the FORALL statement consists

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;

IN Operator The operator IN tests the set membership. This means "equal to any member of." The set may have nulls, but they are ignored. For illustration, the statement below do

Important Distinctions The list of important distinctions are given below: Value versus variable Syntax versus semantics Variable versus variable reference

Quantification in SQL To quantify something, as the theory book has it, is to state its quantity, to say how many of it there are. For example, in Tutorial D the expression CO

1. Create a procedure called TAX_COST_SP to accomplish the tax calculation task. Keep in mind that the state and subtotal values are inputs into the procedure and the procedure is

Using Aliases The Select-list items fetched from a cursor related with the %ROWTYPE should have simple names or, if they are expressions, should have aliases. In the example bel

Row Operators The Row operators return or reference the particular rows. ALL retains the duplicate rows in the result of a query or in an aggregate expression. The DISTINCT el

Question: (a) In the context of database security explain how the following database features help to enforce security in the database system: (i) Authorisation (ii) Access