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
COSMOZOI C THEORY - Richter (1865) proposed the cosmozoic theory that says that life came by spores (cosmozoa) or other particles from other planets on the earth.

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

Perform the following queries on the Hospital1.DB using SQL Anywhere (START EARLY!). a. Which patients have purchased the drug "Tylenol"? List the names and addresses. Arrange the

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

Data Types in SQL - Timestamp TIMESTAMP for values representing points in time on a specified uniform scale. DATE is used for timestamps on a scale of one day, such as DATE '2

Example of Tables within a Table - SQL Example: Obtaining C_ER from COURSE and EXAM_MARK SELECT CourseId, CAST (TABLE (SELECT DISTINCT StudentId, Mark FROM EXAM_MARK AS EM

SQL outer join SELECT * FROM IS_CALLED NATURAL LEFT JOIN IS_ENROLLED_ON Note that adding LEFT to an invocation of CROSS JOIN has no effect unless the right-hand operand

Equivalences & Rewrite Rules: If notice that as well as allowing us to prove trivial theorems, and tautologies enable us to establish that certain sentences are saying the sam

The accuracy of product table data is critical and the Brwebean's. owner wants to have an audit file that contains information regarding all DML activity on the BB_PRODUCT table. T

Parameter and Keyword Description: type_name: This identifies a user-defined type specifier that is used in the subsequent declarations of the objects. AUTHID Clause: