Re: Incorrect PLS-00404

A copy of this was sent to "fumi" <fumi_at_tpts5.seed.net.tw> (if that email address didn't require changing) On 7 Sep 1999 17:44:03 GMT, you wrote:

>
>Thomas Kyte <tkyte_at_us.oracle.com> wrote in message
>news:rr7TN7Y8HWk4lNzDuHGe5DLua1jU_at_4ax.com...
>> A copy of this was sent to "Roy Brokvam" <roy.brokvam_at_conax.com>
>> (if that email address didn't require changing)
>> On Mon, 6 Sep 1999 14:26:17 +0200, you wrote:
>>

[snip]

>
>IMO, I think this is a bug of PL/SQL's compiler (or somewhat undocumented restriction).
>

bug 219936

workaround is to use "of column" when using FOR UPDATE NOWAIT. It is a bug. I just posted the workaround to get them going.

>The purpose of "OF columnlist" is to tell Oracle which tables' rows to be locked.
>If "OF columnlist" is omitted, Oracle locks all referred tables' rows.
>Since Roy Brokvam's SQL statement just refers only 1 table,
>it's not need to specify "OF" option.
>
>In the "PL/SQL User's Guide and Reference", it does not clearly say that
>"OF columnlist" is mandatory before "nowait".
>Beside, there is a sample code in the manual:
>
> DECLARE
> CURSOR c1 IS SELECT empno, sal FROM emp
> WHERE job = 'SALESMAN' AND comm > sal FOR UPDATE NOWAIT;
>
>So, according to the manuals, Roy Brokvam's code should be correct.
>
>Consider the following SQL statements:
>(a) select id, data from tab for update;
>(b) select id, data from tab for update of data;
>(c) select id, data from tab for update nowait;
>(d) select id, data from tab for update of data nowait;
>
>All of the 4 statements are legitimate in SQL.
>But, the statement (c), what Roy Brokvam wrote,
>is unfortunately illegal in PL/SQL (even in Oracle8i).
>
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Sep 07 1999 - 14:37:33 CDT