X

PL/SQL and edition-based redefinition:
the perfect marriage.

  • March 18, 2018

Reserved words, keywords, and the ends of labeled blocks

Steven Feuerstein
Developer Advocate for PL/SQL
@ToonKoppelaars drew our attention to a question posted to oracle-l@freelists.org by knecht.stefan@gmail.com. I’ve paraphrased it thus:

 

<<

…personally I feel that this should not compile. What do you guys think? Simple case in point:

 

package foo is

  procedure bar;

end foo;

 

package body foo as

  procedure bar is

  begin

    begin

      null;

    end loop;

  end bar;

end foo;

 

This compiles and executes without error… in my opinion, with a keyword [sic] like “loop” that should not be happening. In essence, the above is interpreted as:

 

package body foo as

  procedure bar is

  begin

    begin

      null;

    end anon_block_name;

  end bar;

end foo;

 

If we switch the “end loop” to “end if” (which is a similar flow control keyword [sic]) it reacts (in my opinion) correctly:

 

package body foo as

  procedure bar is

  begin

    begin

      null;

    end if;

  end bar;

end foo;

 

and fails to compile. What’s going on? 

>>

 

The morals of this story are:

  1. Unless you use terms of art correctly and with precision you’ll never be able to reason soundly.
  2. Never, ever, ask anyone—and especially yourself—about the behavior of some PL/SQL code before compiling it with warnings turned on. You learn so much about your errors in thinking when you do.
  3. Never try reason about a program whose whole effect is “null”.  The optimizer might subvert the intention in your head because you told it “I do not care what this program does.”

Anyone who’s been to any of my conference talks will have heard me bang on about all of these points. And anyone who hasn’t been to any of my talks should.

 

There are two separable concerns here.

 

(1) “Reserved Words” vs “Keywords”

 

Notice that the Oracle-l questioner doesn’t use these terms of art carefully and precisely. In fact, he doesn’t mention “reserved word” at all. So he’s doomed already.

 

There are those who think that the design of a computer language shouldn’t reserve any words. But it’s too late for that with SQL and PL/SQL. Both have both. At least, to meet its requirement not to break or change the meaning of extant code on upgrade, Oracle never introduces new reserved words.

 

Start this script in SQL*Plus. I ran it in 12.2.

 

Reserved_Words_Keywords_and_the_Ends_of_Labeled_Blocks.sql

 

Notice that p1 compiles without error and draws this warning:

 

PLW-06010: keyword "LOOP" used as a defined name

 

Of course, therefore, it runs without error. Notice that p2 fails to compile with this error:

 

PLS-00103: Encountered the symbol "IF" when expecting one of the following...

 

In other words, it found the reserved word “if” at a syntax spot where it isn’t legal.

 

The point, of course, is that “if” is a reserved word and “loop” is a keyword. See Appendix D, “PL/SQL Reserved Words and Keywords” in the PL/SQL Language Reference book.

 

Notice that p3 compiles clean and runs without error. Which brings us to separable point #2.

 

(2) What you might write in the spot between "end" and the semicolon that finishes the block statement

 

Only procedures, functions, package specs, package bodies, and compound triggers require that the text of the optional identifier between the final “end” and the final semicolon denotes the text of the block’s name. The final example demonstrates the intent, albeit in a contrived, but effective, way.

 

Please check out this enhancement request. I filed it on 31-Mar-2008.

 

ER 6931048 - Implement new warning when block label doesn't match “end ... ;” 

 

Sadly, not a single customer has added their voice to this by by filing a Service Request and explaining their business case. I fear that unless customers start to do this, my ER will remain open.

Join the discussion

Comments ( 3 )
  • Steven Feuerstein Monday, March 19, 2018
    This post is a great reminder of the difference between reserved words and keywords + the importance of taking advantage of compile-time warnings. I loaded Bryn's code onto LiveSQL for easy execution. https://livesql.oracle.com/apex/livesql/file/content_GFFMRZV44RA8SH1PT7VBCSRAX.html
  • Marcus Thursday, July 12, 2018
    Even more perfidious is a missing semicolon combined with commit:

    begin
    doDML();
    end
    commit;
  • Bryn Thursday, July 12, 2018
    Thanks Marcus. Please see Enhancement Request 22293997. I just updated it by adding this:

    [
    Notice how how a silly typo, that one would naively expect to cause a syntax error, allows error-free compilation and produces buggy code. This illustrates the problem:

    procedure p authid Definer is
    begin
    begin
    delete from t where PK < 5;

    -- Notice the accidentally omitted typo following "end".
    end
    commit;
    end p;

    It's easy to demonstrate that the "commit" that the user intended was taken as a comment. However, human proof-reading is very likely indeed to fail to spot such a typo.

    It's very likely that ordinary testing would never reveal the problem because a query to see if the to-be-tested procedure produced the expected result of the DML would probably run the query, in the same session that executed the to-be-tested procedure, and as the test script's next action.

    It's also likely that, in a connection pooling use, the next check-out of the connection would issue a commit, and this would disguise the bug. However, now and then the next check-out of the connection would issue a rollback, and this would result in apparently randomly lost results. This kind of problem is hugely difficult to diagnose.
    ]
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.