What does RESERVED = Y really mean?

Tweet

Recently, I came across the view v$reserved_words. I wanted to know more about this view, so I did a little research and the result was the following list of questions and answers.

What information does v$reserved_words give?

Up to Oracle database version 10g Release 1 (10.1), the definition of this view was:

This view gives a list of all the keywords that are used by the PL/SQL compiler. This view helps developers to determine whether a word is already being used as a keyword in the language.

Starting with Oracle database version 10g Release 2 (10.2), the definition has changed:

This view gives a list of all SQL keywords. To determine whether a particular keyword is reserved in any way, check the RESERVED, RES_TYPE, RES_ATTR, and RES_SEMI columns.

What is the difference between a reserved word and a keyword?

Contrary to keywords, reserved words can never be used as identifiers or as names to user-defined database objects.

Does v$reserved_words contain only reserved words?

No. The view v$reserved_words has a misleading name. It contains ALL keywords used in the Oracle code, not just reserved words. The v$reserved_words.reserved column (10gR2 and above) indicates whether the keyword is actually reserved or not.

What is the definition of the view v$reserved_words in the database (10gR2 and above)?

Query v$fixed_view_definition:

SELECT view_definition FROM v$fixed_view_definition
      WHERE view_name='GV$RESERVED_WORDS'

to get the definition of the view:

SELECT inst_id,
  keyword,
  LENGTH,
  decode(MOD(TRUNC(type / 2),   2),   0,   'N',   1,   'Y',   '?') reserved,
  decode(MOD(TRUNC(type / 4),   2),   0,   'N',   1,   'Y',   '?') res_type,
  decode(MOD(TRUNC(type / 8),   2),   0,   'N',   1,   'Y',   '?') res_attr,
  decode(MOD(TRUNC(type / 16),   2),   0,   'N',   1,   'Y',   '?') res_semi,
  decode(MOD(TRUNC(type / 32),   2),   0,   'N',   1,   'Y',   '?') duplicate
FROM x$kwddef

What is the meaning of each column in the view v$reserved_words (10gR2 and above)?

• KEYWORD: Name of the keyword
• LENGTH: Length of the keyword
• RESERVED: A value of Y means that the keyword cannot be used as an identifier. A value of N means that it is not reserved.
• RES_TYPE: A value of Y means that the keyword cannot be used as a type name. A value of N means that it is not reserved.
• RES_ATTR: A value of Y means that the keyword cannot be used as an attribute name. A value of N means that it is not reserved.
• RES_SEMI: A value of Y means that the keyword is not allowed as an identifier in certain situations, such as in DML. A value of N means that it is not reserved.
• DUPLICATE: A value of Y means that the keyword is a duplicate of another keyword. A value of N means that it is not a duplicate.

How can I list the keywords that I CANNOT use as identifiers in my PL/SQL program?

The output of the following PL/SQL code gives you the list of keywords that you cannot declare as identifiers (Connected to Oracle 10gR2 Express Edition):

BEGIN
   FOR rec IN
      (SELECT keyword,
          reserved
       FROM v$reserved_words
       ORDER BY keyword)
   LOOP
      BEGIN
         EXECUTE IMMEDIATE 'declare ' || rec.keyword || ' number; 
          begin 
            null; 
          end;';

      EXCEPTION
      WHEN others THEN
         IF rec.reserved = 'Y' THEN
            DBMS_OUTPUT.PUT_LINE(rec.keyword || ' - RESERVED');
         ELSE
            DBMS_OUTPUT.PUT_LINE(rec.keyword);
         END IF;

      END;
   END LOOP;
END;

Output:

! - RESERVED
& - RESERVED
( - RESERVED
) - RESERVED
* - RESERVED
+ - RESERVED
, - RESERVED
- - RESERVED
. - RESERVED
/ - RESERVED
: - RESERVED
< - RESERVED
<<
= - RESERVED
> - RESERVED
@ - RESERVED
ALL - RESERVED
ALTER - RESERVED
AND - RESERVED
ANY - RESERVED
AS - RESERVED
ASC - RESERVED
AT
BEGIN
BETWEEN - RESERVED
BY - RESERVED
CASE
... 

But, don’t you observe something weird here?

Yes, I do. There are keywords that are NOT reserved, yet they CANNOT be used as identifiers, like BEGIN and TYPE for example.

Are there any RESERVED keywords that CAN BE used as identifiers?

Yes.

BEGIN
   FOR rec IN
      (SELECT keyword,
          reserved
       FROM v$reserved_words
       WHERE reserved = 'Y'
       ORDER BY keyword)
   LOOP
      BEGIN
         EXECUTE IMMEDIATE 'declare ' || rec.keyword || ' number; 
              begin 
                null; 
              end;';
         DBMS_OUTPUT.PUT_LINE(rec.keyword || ' - RESERVED');
      EXCEPTION
      WHEN others THEN
         NULL;
      END;
   END LOOP;
END;

Output:

CHAR - RESERVED
DATE - RESERVED
DECIMAL - RESERVED
FLOAT - RESERVED
INTEGER - RESERVED
LONG - RESERVED
PCTFREE - RESERVED
RAW - RESERVED
RENAME - RESERVED
SET - RESERVED
SMALLINT - RESERVED
SYNONYM - RESERVED
TRIGGER - RESERVED
VARCHAR - RESERVED
VARCHAR2 - RESERVED

So, for example, you can declare something like this without generating an error:

DECLARE 
  char NUMBER;
  date VARCHAR2(1);
  integer VARCHAR2(1);
BEGIN
   char := 1;
   date := 'e';
   integer := 'r';
END;

But why would you use keywords, reserved or not, as identifiers?!

Now, I’ve lost my confidence in RESERVED. What does RESERVED = ‘Y’ really mean?

Let me know when you find the answer to this question.

Is there any way to use a SQL reserved word as a name for a database object?

Again, why would you want to do that? but to answer your question, just enclose the reserved word with quotation marks:

EDDIE@XE> create table create (x number);
create table create (x number)
             *
ERROR at line 1:
ORA-00903: invalid table name


EDDIE@XE> create table "create" (x number);

Table created.

EDDIE@XE> desc create;
ERROR:
ORA-00931: missing identifier


EDDIE@XE> desc "create";
 Name                                      Null?    Type
 ----------------------------------------- -------- ------

 X                                                  NUMBER

Sources and resources:

• When Your Bind Variable is Invalid and Your Column is Weird
• I was stumbling upon…
• Reserved Words – 10g vs 9i
• Oracle Database Reserved Words

Documentation:

• 8.1.7
• 9.0.1
• 9.2
• 10.1
• 10.2

SQL and PL/SQL reserved words:

• Oracle Database 10.2 Reserved Words

• PL/SQL (10.2) Reserved Words and Keywords

Metalink:

• How to Determine Reserved Words

Related articles:

• The Case of Better Readable Code
• You Don’t Have to Use an Aggregator to Keep Track of Your Favorite Topic
• When Your Bind Variable is Invalid and Your Column is Weird

Filed in Oracle on 10 Jan 07 | Tags: keywords, reserved, v$

---