Why are there two "null" keywords in Oracle's v$reserved_words view?

Question

I noticed that the v$reserved_words view in our Oracle 11.2 database has two rows with a keyword of null (the value null, not the word null).

So this query:

select * from v$reserved_words where keyword is null;

Returns two rows, identical except one row has reserved = Y and the other reserved = N. Any idea what the purpose of this is or how it's used?

Answer 1

You need to understand how to interpret the view v$reserved _words. To determine whether a particular keyword is reserved in any way, check the RESERVED, RES_TYPE, RES_ATTR, and RES_SEMI columns.

SQL> column keyword format A10;
SQL> select * from v$reserved_words where keyword is null;

KEYWORD        LENGTH R R R R D     CON_ID
---------- ---------- - - - - - ----------
                    0 Y N N N N          0
                    0 N N N N N          0

Those two rows does not have 'Y' for all the columns. Yes, one of the row has RESERVED as 'Y' but the length is 0. Also, none of the attributes are 'Y'.

From documentation,

RESERVED    VARCHAR2(1) 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    VARCHAR2(1) 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    VARCHAR2(1) 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    VARCHAR2(1) 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   VARCHAR2(1) 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.

That clarifies the reason.

instead of v$reserved_words, you can also check for the SQL and PL/SQL reserved words in SQL*Plus in the following way:

SQL*Plus: Release 12.1.0.1.0 Production on Fri Sep 5 13:05:15 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Thu Sep 04 2014 15:01:52 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> help reserve

 RESERVED WORDS (PL/SQL)
 -----------------------

 PL/SQL Reserved Words have special meaning in PL/SQL, and may not be used
 for identifier names (unless enclosed in "quotes").

 An asterisk (*) indicates words are also SQL Reserved Words.

 ALL*            DESC*           JAVA            PACKAGE         SUBTYPE
 ALTER*          DISTINCT*       LEVEL*          PARTITION       SUCCESSFUL*
 AND*            DO              LIKE*           PCTFREE*        SUM
 ANY*            DROP*           LIMITED         PLS_INTEGER     SYNONYM*
 ARRAY           ELSE*           LOCK*           POSITIVE        SYSDATE*
 AS*             ELSIF           LONG*           POSITIVEN       TABLE*
 ASC*            END             LOOP            PRAGMA          THEN*
 AT              EXCEPTION       MAX             PRIOR*          TIME
 AUTHID          EXCLUSIVE*      MIN             PRIVATE         TIMESTAMP
 AVG             EXECUTE         MINUS*          PROCEDURE       TIMEZONE_ABBR
 BEGIN           EXISTS*         MINUTE          PUBLIC*         TIMEZONE_HOUR
 BETWEEN*        EXIT            MLSLABEL*       RAISE           TIMEZONE_MINUTE
 BINARY_INTEGER  EXTENDS         MOD             RANGE           TIMEZONE_REGION
 BODY            EXTRACT         MODE*           RAW*            TO*
 BOOLEAN         FALSE           MONTH           REAL            TRIGGER*
 BULK            FETCH           NATURAL         RECORD          TRUE
 BY*             FLOAT*          NATURALN        REF             TYPE
 CHAR*           FOR*            NEW             RELEASE         UI
 CHAR_BASE       FORALL          NEXTVAL         RETURN          UNION*
 CHECK*          FROM*           NOCOPY          REVERSE         UNIQUE*
 CLOSE           FUNCTION        NOT*            ROLLBACK        UPDATE*
 CLUSTER*        GOTO            NOWAIT*         ROW*            USE
 COALESCE        GROUP*          NULL*           ROWID*          USER*
 COLLECT         HAVING*         NULLIF          ROWNUM*         VALIDATE*
 COMMENT*        HEAP            NUMBER*         ROWTYPE         VALUES*
 COMMIT          HOUR            NUMBER_BASE     SAVEPOINT       VARCHAR*
 COMPRESS*       IF              OCIROWID        SECOND          VARCHAR2*
 CONNECT*        IMMEDIATE*      OF*             SELECT*         VARIANCE
 CONSTANT        IN*             ON*             SEPERATE        VIEW*
 CREATE*         INDEX*          OPAQUE          SET*            WHEN
 CURRENT*        INDICATOR       OPEN            SHARE*          WHENEVER*
 CURRVAL         INSERT*         OPERATOR        SMALLINT*       WHERE*
 CURSOR          INTEGER*        OPTION*         SPACE           WHILE
 DATE*           INTERFACE       OR*             SQL             WITH*
 DAY             INTERSECT*      ORDER*          SQLCODE         WORK
 DECIMAL*        INTERVAL        ORGANIZATION    SQLERRM         WRITE
 DECLARE         INTO*           OTHERS          START*          YEAR
 DEFAULT*        IS*             OUT             STDDEV          ZONE
 DELETE*         ISOLATION


 RESERVED WORDS (SQL)
 --------------------

 SQL Reserved Words have special meaning in SQL, and may not be used for
 identifier names unless enclosed in "quotes".

 An asterisk (*) indicates words are also ANSI Reserved Words.

 Oracle prefixes implicitly generated schema object and subobject names
 with "SYS_". To avoid name resolution conflict, Oracle discourages you
 from prefixing your schema object and subobject names with "SYS_".

 ACCESS          DEFAULT*         INTEGER*        ONLINE          START
 ADD*            DELETE*          INTERSECT*      OPTION*         SUCCESSFUL
 ALL*            DESC*            INTO*           OR*             SYNONYM
 ALTER*          DISTINCT*        IS*             ORDER*          SYSDATE
 AND*            DROP*            LEVEL*          PCTFREE         TABLE*
 ANY*            ELSE*            LIKE*           PRIOR*          THEN*
 AS*             EXCLUSIVE        LOCK            PRIVILEGES*     TO*
 ASC*            EXISTS           LONG            PUBLIC*         TRIGGER
 AUDIT           FILE             MAXEXTENTS      RAW             UID
 BETWEEN*        FLOAT*           MINUS           RENAME          UNION*
 BY*             FOR*             MLSLABEL        RESOURCE        UNIQUE*
 CHAR*           FROM*            MODE            REVOKE*         UPDATE*
 CHECK*          GRANT*           MODIFY          ROW             USER*
 CLUSTER         GROUP*           NOAUDIT         ROWID           VALIDATE
 COLUMN          HAVING*          NOCOMPRESS      ROWNUM          VALUES*
 COMMENT         IDENTIFIED       NOT*            ROWS*           VARCHAR*
 COMPRESS        IMMEDIATE*       NOWAIT          SELECT*         VARCHAR2
 CONNECT*        IN*              NULL*           SESSION*        VIEW*
 CREATE*         INCREMENT        NUMBER          SET*            WHENEVER*
 CURRENT*        INDEX            OF*             SHARE           WHERE
 DATE*           INITIAL          OFFLINE         SIZE*           WITH*
 DECIMAL*        INSERT*          ON*             SMALLINT*


SQL>

Adding further information to clarify those two rows are different TYPE.

The support notes in My Oracle Support here https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=csshtn48w_9&_afrLoop=2343138470228212#990809 says it is better to check the TYPE in View:X$KWDDEF

SQL> select indx, keyword, length, type from X$KWDDEF where keyword is NULL;

      INDX KEYWORD        LENGTH       TYPE
---------- ---------- ---------- ----------
      2087                     0          2
      2088                     0          1

Now how is the V$RESERVED_WORDS view formed from X$KWDDEF? This is the underlying query :

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;

So what is TYPE actually? The TYPE column is used as a bucket for grouping words.

SQL> select type, count(*)
  2      from x$kwddef
  3      group by type
  4      order by 1
  5  /

      TYPE   COUNT(*)
---------- ----------
         1       1939
         2         96
         9          2
        16         28
        33         20
        34          4

6 rows selected.

So, based on TYPE(as Oracle suggests), the two rows are not the same. They belong to different TYPEs.

The keyword 'NULL' shouldn't be misinterpreted with the NULL VALUE in those rows. NULL keyword is entirely different and has LENGTH = 4.

SQL> select indx, keyword, length, type from X$KWDDEF where keyword = 'NULL';

      INDX KEYWORD        LENGTH       TYPE
---------- ---------- ---------- ----------
       338 NULL                4          2

Since 'X$KWDDEF' has an entry for 'NULL' keyword as TYPE 2, these two rows could be safely ignored. I guess X$KWDDEF means Kernel word definition, just a guess!