V$RESERVED_WORDS & X$KWDDEF - Interesting facts!
It's a lazy Friday and I saw an interesting question in Stack Overflow about "Why are there two “null” keywords in Oracle's v$reserved_words view?" And I spent next 2 hours digging into knowing the reason. Here are my findings:
We 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.We can also check for the SQL and PL/SQL reserved words/keywords in SQL*Plus in the following way:
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>
Now, the interesting thing starts :
The support notes in My Oracle Support here Support and Historical Notes for "V$RESERVED_WORDS" 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.
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!
Earlier in the morning I wrote an article here http://lalitkumarb.wordpress.com/2014/09/05/vreserved_words-xkwddef-interesting-facts/ Thought my findings deserve to be in OraFAQ blog too :-)
Regards,
Lalit
Personal blog : http://lalitkumarb.wordpress.com/
- Lalit Kumar B's blog
- Log in to post comments