RE: WHERE CURRENT OF Question

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:

<SQL Manual snippet>
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. <End snippet>

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 [mailto: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).