FAQ
Why does the following work? I open a cursor with FOR UPDATE OF COLUMN_A
and then do an update of COLUMNB, WHERE CURRENT OF the cursor. Surely I
shouldn't be allowed to do that?
(Ora 8.1.7.2)

Any insight appreciated!
Thanks
- Bill.

SQLWKS> create table test_table

2> (
3> column_a number,
4> column_b number
5> )
6>

Statement processed.
SQLWKS> insert into test_table values (1,2)

2>
1 row processed.
SQLWKS> insert into test_table values (2,3)

2>
1 row processed.
SQLWKS> insert into test_table values (3,4)

2>
1 row processed.
SQLWKS> declare

2> cursor c1 is select * from test_table for update of column_a;
3> begin
4> for i in c1 loop
5> update test_table set column_b = column_b * 2
6> where current of c1;
7> end loop;
8> end;
9>

Statement processed.
SQLWKS> select *

2> from test_table
3>

COLUMN_A COLUMN_B

---------- ----------

1 4
2 6
3 8

3 rows selected.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bill Buchan
INET: wbuchan_at_uk.intasys.com

Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

Search Discussions

  • Steve Adams at Oct 5, 2001 at 4:45 pm
    Hi Bill,

    The FOR UPDATE clause syntax allows for the possibility of column-level locking, but
    Oracle only implements row-level
    locking. So the OF phrase is just ignored.

    @ Regards,
    @ Steve Adams
    @ http://www.ixora.com.au/
    @ http://www.christianity.net.au/

    -----Original Message-----
    Sent: Saturday, 6 October 2001 2:40
    To: Multiple recipients of list ORACLE-L

    Why does the following work? I open a cursor with FOR UPDATE OF COLUMN_A
    and then do an update of COLUMNB, WHERE CURRENT OF the cursor. Surely I
    shouldn't be allowed to do that?
    (Ora 8.1.7.2)

    Any insight appreciated!
    Thanks
    - Bill.

    SQLWKS> create table test_table

    2> (
    3> column_a number,
    4> column_b number
    5> )
    6>

    Statement processed.
    SQLWKS> insert into test_table values (1,2)

    2>
    1 row processed.
    SQLWKS> insert into test_table values (2,3)

    2>
    1 row processed.
    SQLWKS> insert into test_table values (3,4)

    2>
    1 row processed.
    SQLWKS> declare

    2> cursor c1 is select * from test_table for update of column_a;
    3> begin
    4> for i in c1 loop
    5> update test_table set column_b = column_b * 2
    6> where current of c1;
    7> end loop;
    8> end;
    9>

    Statement processed.
    SQLWKS> select *

    2> from test_table
    3>

    COLUMN_A COLUMN_B

    ---------- ----------

    1 4
    2 6
    3 8

    3 rows selected.

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Bill Buchan
    INET: wbuchan_at_uk.intasys.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Steve Adams
    INET: steve.adams_at_ixora.com.au

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Larry Elkins at Oct 5, 2001 at 4:57 pm
    Bill,

    The name of the column(s) specified is only there to specify which table(s)
    rows to lock. The SQL manual says it better (and more concisely) than I can:

    OF Locks the select rows only for a particular table in a join. The columns
    in the OF clause only specify which tables' rows are locked. The specific
    columns of the table that you specify are not significant. If you omit this
    clause, Oracle locks the selected rows from all the tables in the query.

    So, people might specify it if the are selecting from two tables but only
    want to lock one. And lots of folks do it as "documentation" of what they
    intend to do. And since it's Friday, I will go slightly OT and mention I
    that I can't remember which version of Forms, I think an early version of
    4.5, the client side PL/SQL parser when compiling would let you specify
    *anything* in the OF clause -- e.g. "select...from emp for update of
    IM_NOT_A_COLUMN". But, when executing the code, it would croak with an
    invalid column error.

    Regards,

    Larry G. Elkins
    elkinsl_at_flash.net
    214.954.1781
    -----Original Message-----
    From: root_at_fatcity.com On Behalf Of Bill Buchan
    Sent: Friday, October 05, 2001 11:40 AM
    To: Multiple recipients of list ORACLE-L
    Subject: WHERE CURRENT OF Question




    Why does the following work? I open a cursor with FOR UPDATE OF COLUMN_A
    and then do an update of COLUMNB, WHERE CURRENT OF the cursor. Surely I
    shouldn't be allowed to do that?
    (Ora 8.1.7.2)

    Any insight appreciated!
    Thanks
    - Bill.


    SQLWKS> create table test_table
    2> (
    3> column_a number,
    4> column_b number
    5> )
    6>
    Statement processed.
    SQLWKS> insert into test_table values (1,2)
    2>
    1 row processed.
    SQLWKS> insert into test_table values (2,3)
    2>
    1 row processed.
    SQLWKS> insert into test_table values (3,4)
    2>
    1 row processed.
    SQLWKS> declare
    2> cursor c1 is select * from test_table for update
    of column_a;
    3> begin
    4> for i in c1 loop
    5> update test_table set column_b = column_b * 2
    6> where current of c1;
    7> end loop;
    8> end;
    9>
    Statement processed.
    SQLWKS> select *
    2> from test_table
    3>
    COLUMN_A COLUMN_B
    ---------- ----------
    1 4
    2 6
    3 8
    3 rows selected.


    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Bill Buchan
    INET: wbuchan_at_uk.intasys.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Larry Elkins
    INET: elkinsl_at_flash.net

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Viktor at Oct 5, 2001 at 5:31 pm
    Hi Bill,

    I didn't really do much checking, but my guess is that
    the 'WHERE CURRENT OF' works here because you're
    selecting * in the cursor.

    If you, for instance, select columna_a as supposed to
    *
    then you will most likely get the error.

    My guess is that this is just how ORACLE locks the
    set when you select in the cursor; therefore, you
    don't get the error. But,like I said, if you only
    select column_a, and then, attempt to update column_b,
    you will most likely see the error that you might have
    expected.

    Regards.

    Bill Buchan wrote:

    Why does the following work? I open a cursor with
    FOR UPDATE OF COLUMN_A
    and then do an update of COLUMNB, WHERE CURRENT OF
    the cursor. Surely I
    shouldn't be allowed to do that?
    (Ora 8.1.7.2)

    Any insight appreciated!
    Thanks
    - Bill.


    SQLWKS> create table test_table
    2> (
    3> column_a number,
    4> column_b number
    5> )
    6>
    Statement processed.
    SQLWKS> insert into test_table values (1,2)
    2>
    1 row processed.
    SQLWKS> insert into test_table values (2,3)
    2>
    1 row processed.
    SQLWKS> insert into test_table values (3,4)
    2>
    1 row processed.
    SQLWKS> declare
    2> cursor c1 is select * from
    test_table for update of column_a;
    3> begin
    4> for i in c1 loop
    5> update test_table set
    column_b = column_b * 2
    6> where current of c1;
    7> end loop;
    8> end;
    9>
    Statement processed.
    SQLWKS> select *
    2> from test_table
    3>
    COLUMN_A COLUMN_B
    ---------- ----------
    1 4
    2 6
    3 8
    3 rows selected.


    --
    Please see the official ORACLE-L FAQ:
    http://www.orafaq.com
    --
    Author: Bill Buchan
    INET: wbuchan_at_uk.intasys.com

    Fat City Network Services -- (858) 538-5051 FAX:
    (858) 538-5051
    San Diego, California -- Public Internet
    access / Mailing Lists
    To REMOVE yourself from this mailing list, send an
    E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in
    the message BODY, include a line containing: UNSUB
    ORACLE-L
    (or the name of mailing list you want to be removed
    from). You may
    also send the HELP command for other information
    (like subscribing).
    Do You Yahoo!?
    NEW from Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month.
    http://geocities.yahoo.com/ps/info1

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Viktor
    INET: stant_98_at_yahoo.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 5, '01 at 3:53p
activeOct 5, '01 at 5:31p
posts4
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase