Approaches to avoiding reserved words

Finding a valid name for your identifier should be the least of your problems, as there are thousands and thousands of permutations of the legal characters. The question is, how will you know if you inadvertently use a reserved word in your own program? By some counts, there are hundreds of words known to PL/SQL!

You could just ignore this issue and deal with the compiler errors as they come up. This is what a lot of people do, but it can be a real drain on a workday because sometimes the error messages are completely off the wall. Some people use a high-priced development environment with a snazzy syntax-directed editor that alerts you to possible problems as you type. Personally, my favorite solution is to use a cheap (as in free) editor that is aware of PL/SQL's keywords and just highlights them automatically.^[2]

^[2] As a matter of fact, I helped create a syntax highlighting file and an automatic indentation file for an open source editor known as vim (derived from vi). Very cool. Visit the book's web site for details.

I have compiled a small list of the Oracle9i PL/SQL reserved words, shown in Table 3-4 (and available in the reserved.txt file on the O'Reilly site). Starting from the list of keywords Oracle publishes in the V$RESERVED_WORDS data dictionary view, I tried to declare a variable, and then a procedure, using the word as its identifier. If the compiler prevented me doing from one or both of those operations, I put the keyword on the list.

Table 3-4. Absolute minimal list of words to avoid using as PL/SQL identifiers
ACCESSADDALLALTERANDANYASASCATAUDITBEGINBETWEENBYCASECHARCHECKCLOSECLUSTERCOLUMNCOLUMNSCOMMENTCOMMITCOMPRESSCONNECTCREATECURRENTDEFAULT	CURSORDATEDECIMALDECLAREDEFAULTDELETEDESCDISTINCTDROPELSEENDEXCLUSIVEEXISTSFILEFLOATFORFROMFUNCTIONGRANTGROUPHAVINGIDENTIFIEDIFIMMEDIATEININCREMENTINDEX	INDEXESINITIALINSERTINTEGERINTERSECTINTOISLEVELLIKELOCKLONGMAXEXTENTSMINUSMLSLABELMODEMODIFYNOAUDITNOCOMPRESSNOTNOWAITNULLNUMBEROFOFFLINEONONLINEOPEN	OPTIONORORDEROVERLAPSPACKAGEPCTFREEPRIORPRIVILEGESPROCEDUREPUBLICRAWRENAMERESOURCERETURNREVOKEROLLBACKROWROWIDROWNUMROWSSAVEPOINTSELECTSESSIONSETSHARESIZESMALLINT	STARTSUCCESSFULSYNONYMSYSDATETABLETHENTOTRIGGERTYPEUIDUNIONUNIQUEUPDATEUSEUSERVALIDATEVALUESVARCHARVARCHAR2VIEWWHENWHENEVERWHEREWITH

If you are running Oracle 8.1.5 or later, you can ask your DBA to grant you privilege to execute this statement:

SQL> SELECT * FROM V$RESERVED_WORDS;

Although the list is overwhelmingly long, it does not even include all the identifiers in STANDARD; the PL/SQL compiler does not care if you carelessly reuse identifiers declared there. All the more reason to use an intelligent editor.