Re: Incorrect PLS-00404

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:
>
> >Oracle7 Server 7.3.4.1.0
> >PL/SQL 2.3.4.1.0
> >
> >My package is rejected by the database! I am certain that the syntax is
> >correct:
> >
> >SQL> CREATE OR REPLACE PACKAGE mytest AS
> > 2 FUNCTION func ( p_id NUMBER ) RETURN NUMBER;
> > 3 END mytest;
> > 4 /
> >Package created.
> >SQL>
> >SQL> CREATE OR REPLACE PACKAGE BODY mytest AS
> > 2 CURSOR cur ( p_id NUMBER ) IS
> > 3 SELECT id,data
> > 4 FROM tab
> > 5 FOR UPDATE
> of data
>
> it (plsql) was looking for the OF columnname in addition to the for update
>
>
> > 6 NOWAIT;

[snip]
> >-------- -----------------------------------------------------------------
> >13/3 PL/SQL: SQL Statement ignored
> >14/27 PLS-00404: cursor 'MYTEST.CUR' must be declared with FOR UPDATE
> > to use with CURRENT OF

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

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). Received on Tue Sep 07 1999 - 12:44:03 CDT