A PL/SQL compiler bug?

From: Jurij Modic <jmodic_at_src.si>
Date: Tue, 03 Nov 1998 22:55:50 GMT
Message-ID: <363f7edf.2540728@news.siol.net>

I think I encountered a bug in PL/SQL compiler. I tested this on versions 7.3.4 and 7.3.2. The exmples I provide here are extremely simplified, but the real consequences can be quite harmfull.

Consider the following anonymous block which uses loop label at the END LOOP statement.

SQL> BEGIN
  2 FOR i IN 1..10 LOOP
  3 NULL;
  4 END LOOP loop_label;
  5 END;
  6 /

PL/SQL procedure successfully completed.

As expected, this compiled and executed correctly. Now I replace the loop label with a reserved word and as expected, the block doesn't compile.

SQL> BEGIN
  2 FOR i IN 1..10 LOOP
  3 NULL;
  4 END LOOP if;
  5 END;
  6 /
  END LOOP if;

*
ERROR at line 4:

ORA-06550: line 4, column 12:
PLS-00103: Encountered the symbol "IF" when expecting one of the
following:

; <an identifier> <a double-quoted delimited-identifier> The symbol "IF" was ignored.

So compiler doesn't allow a reserved word "IF" to be used as a loop label. It also refuses other reserved words, like BEGIN, INSERT, DECLARE etc. This is of course the expected behaviour.

Now I replace the word "if" with the word "commit":

SQL> BEGIN
  2 FOR i IN 1..10 LOOP
  3 NULL;
  4 END LOOP commit;
  5 END;
  6 /

PL/SQL procedure successfully completed.

!!!!????? It compiled! Although I think it should not. It also accepts ROLLBACK as a loop label. And of course the COMMIT is taken as a label, not as a COMMIT statement.

But the real problem is not if you are allowed to use reserved words as labels - it is how compiler might take a perfectly legal transaction control statement as COMMIT or ROLLBACK as a label if you missed a semicolon after an END LOOP statement. This was exactly what happened to me today. In a quite large stored procedure with fairly complicated logic handling a large amount of data I had something like this:

  LOOP
    LOOP
      <some PL/SQL logic, lots of INSERTS/UPDATES/DELETES>     END LOOP
    COMMIT;
  END LOOP;
  COMMIT;
END; Note I missed a semicolon after the first END LOOP and so the compiler took the first COMMIT to be a lable to that loop! It compiled succesfuly and as during execution no commits were performed inside a loop my rollback segment bombed out after a couple of hours of execution! Not to mention that it took me more than an hour to find out the missing semicolumn was a reason why COMMITs didn't ocurred as they should inside a loop.

Can anyone share her/his opinion about this behaviour? Should this be reported as a bug?

TIA for any response,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

The above opinions are mine and do not represent any official standpoints of my employer Received on Tue Nov 03 1998 - 16:55:50 CST