Home » SQL & PL/SQL » SQL & PL/SQL » How to lock rows fetched by inline view (Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production, Linux)
How to lock rows fetched by inline view [message #360500] Fri, 21 November 2008 03:15 Go to next message
v.ram81
Messages: 50
Registered: April 2006
Location: pune
Member

Hi,
I want to lock rows fetched by inline view used in cursor. I am doing something like this -

SQL> SELECT        empno, ename, sal
  2  FROM          (SELECT   empno, ename, sal
  3                 FROM     emp
  4                 ORDER BY empno)
  5  WHERE         ROWNUM < 1000
  6  FOR UPDATE OF sal NOWAIT;
FROM          (SELECT   empno, ename, sal
              *
ERROR at line 2:
ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.


But getting error ORA-02014.
I can do it like --

SQL> select empno,ename,sal
  2  from emp
  3  where empno in (SELECT        empno
  4  FROM          (SELECT   empno
  5                 FROM     emp
  6                 ORDER BY empno)
  7  WHERE         ROWNUM < 1000)
  8  order by empno
  9  for update of sal nowait;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7499 ALLEN            1600
      7521 WARD             1250
      7566 JONES            2975
      7654 MARTIN           1250
      7698 BLAKE            2850
      7782 CLARK            2450
      7788 SCOTT            3000
      7839 KING             5000
      7844 TURNER           1500
      7876 ADAMS            1100

     EMPNO ENAME             SAL
---------- ---------- ----------
      7900 JAMES             950
      7902 FORD             3000
      7934 MILLER           1300

14 rows selected.


But If I want to lock rows getting selected in Inline view then what should i do ?
What should be the approach?
Please suggest.
Re: How to lock rows fetched by inline view [message #360503 is a reply to message #360500] Fri, 21 November 2008 03:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't but you already have a workaround.

Regards
Michel
Re: How to lock rows fetched by inline view [message #360504 is a reply to message #360500] Fri, 21 November 2008 03:45 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

No. You can ...

SQL> SELECT        empno, ename, sal
  2           FROM (SELECT empno, ename, sal
  3                   FROM emp)
  4          WHERE ROWNUM < 1000
  5       ORDER BY empno
  6  FOR UPDATE OF sal NOWAIT;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7499 ALLEN            1600
      7521 WARD             1250
      7566 JONES            2975
      7654 MARTIN           1250
      7698 BLAKE            2850
      7782 CLARK            2450
      7788 SCOTT            3000
      7839 KING             5000
      7844 TURNER           1500
      7876 ADAMS            1100
      7900 JAMES             950
      7902 FORD             3000
      7934 MILLER           1300

14 rows selected.

SQL>


Smile
Rajuvan.
Re: How to lock rows fetched by inline view [message #360507 is a reply to message #360500] Fri, 21 November 2008 03:57 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
It works if you move the order by outside of the inline view, but then you'd be getting 1000 random rows instead of the 1st 1000 by empno.
Re: How to lock rows fetched by inline view [message #360509 is a reply to message #360504] Fri, 21 November 2008 04:08 Go to previous messageGo to next message
v.ram81
Messages: 50
Registered: April 2006
Location: pune
Member

Thank you Rajuvan,Michel for your time and solution.
For this query it is working .
But I can't put Order By clause outside of inline view in real example.The result changes.
Ok thanks anyway.

Thanks and Regards,
Ram.

Re: How to lock rows fetched by inline view [message #360621 is a reply to message #360509] Fri, 21 November 2008 11:59 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Instead of limiting the rows when you declare your cursor, you can limit them when you loop through your cursor and fetch them, as demonstrated below. This will allow you to update only the correct rows, but it will temporarily lock all of them.

SCOTT@orcl_11g> SELECT empno, ename, sal
  2  FROM   emp
  3  ORDER  BY empno
  4  /

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7499 ALLEN            1600
      7521 WARD             1250
      7566 JONES            2975
      7654 MARTIN           1250
      7698 BLAKE            2850
      7782 CLARK            2450
      7788 SCOTT            3000
      7839 KING             5000
      7844 TURNER           1500
      7876 ADAMS            1100
      7900 JAMES             950
      7902 FORD             3000
      7934 MILLER           1300

14 rows selected.

SCOTT@orcl_11g> DECLARE
  2  	CURSOR c1 IS
  3  	SELECT empno, ename, sal
  4  	FROM   emp
  5  	ORDER  BY empno
  6  	FOR UPDATE OF sal NOWAIT;
  7  	v_emp  c1%ROWTYPE;
  8  BEGIN
  9  	OPEN c1;
 10  	FOR i IN 1 .. 4 LOOP
 11  	   FETCH c1 INTO v_emp;
 12  	   EXIT WHEN c1%NOTFOUND;
 13  	   UPDATE emp
 14  	   SET	  sal = sal * 1.02
 15  	   WHERE  CURRENT OF c1;
 16  	END LOOP;
 17  END;
 18  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT empno, ename, sal
  2  FROM   emp
  3  ORDER  BY empno
  4  /

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             816
      7499 ALLEN            1632
      7521 WARD             1275
      7566 JONES          3034.5
      7654 MARTIN           1250
      7698 BLAKE            2850
      7782 CLARK            2450
      7788 SCOTT            3000
      7839 KING             5000
      7844 TURNER           1500
      7876 ADAMS            1100
      7900 JAMES             950
      7902 FORD             3000
      7934 MILLER           1300

14 rows selected.

SCOTT@orcl_11g> ROLLBACK
  2  /

Rollback complete.

SCOTT@orcl_11g> 


[Updated on: Fri, 21 November 2008 12:01]

Report message to a moderator

Re: How to lock rows fetched by inline view [message #360683 is a reply to message #360621] Sat, 22 November 2008 03:25 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Barbara, I thought locks happened at execution time, not at fetch time. If I'm right, this would have the effect of locking every row in the table.

I would stick with the work-around.

Ross Leishman
Re: How to lock rows fetched by inline view [message #360697 is a reply to message #360683] Sat, 22 November 2008 11:51 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
rleishman wrote on Sat, 22 November 2008 01:25
Barbara, I thought locks happened at execution time, not at fetch time. If I'm right, this would have the effect of locking every row in the table.
Ross Leishman


Yes, and I said, "it will temporarily lock all of them" when I posted it.

rleishman wrote on Sat, 22 November 2008 01:25
I would stick with the work-around.

Ross Leishman


Agreed. I wasn't recommending what I posted, just pointing out another option and its drawback.

Re: How to lock rows fetched by inline view [message #360707 is a reply to message #360697] Sat, 22 November 2008 20:06 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
My apologies, I really should read more carefully.
Previous Topic: SQL statement - think I need to use joins
Next Topic: Need help to find Business day number of the month (merged)
Goto Forum:
  


Current Time: Fri Dec 09 13:58:40 CST 2016

Total time taken to generate the page: 0.10492 seconds