Skip to Main Content
  • Questions

  • Why are most of keywords in V$RESERVED_WORDS not actually reserved?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Dmitriy.

Asked: January 08, 2025 - 4:39 pm UTC

Last updated: January 14, 2025 - 6:43 pm UTC

Version: 19c

Viewed 1000+ times

You Asked

Hello,

Could you please clarify the meaning of the view V$RESERVED_WORDS and especially its contents?
At first glance, the documentation has a clear explanation. The view contains reserved words, and columns RESERVED, RES_TYPE, RES_ATTR, and RES_SEMI describe, for which purposes these words can or cannot be used.
But more than 90% of words are not actually reserved. In my case (Oracle Database 19c) the view contains 2491 words, and 2361 of them have value 'N' in all columns. Even if we exclude duplicates, it is still 2339 words:

select count(*) 
  from v$reserved_words
 where reserved = 'N'
   and res_type = 'N'
   and res_attr = 'N'
   and res_semi = 'N'
   and duplicate = 'N'
;

  COUNT(*)
----------
      2339


What is the risk of using these words as object names or as variables in PL/SQL?

and Chris said...

If a keyword is reserved you can't use it as an (unquoted) identifier. For example, this fails:

create table select ( c1 int );

ORA-00903: invalid table name


You can overcome this by always quoting identifiers:

create table "select" ( c1 int );

select count(*) from "select";

  COUNT(*)
----------
         0

drop table "select";


But this best avoided; quoting all identifiers is tedious and easy to overlook.

This makes it extremely risky to make new keywords reserved words. Any existing applications with object names matching the new keyword will break.

So it's rare new keywords will also be reserved. That said, some clients may struggle to parse queries with table or column names matching new keywords. If this happens, the solution is the same as with reserved words - quote them.

Having names that match keywords can also make your SQL harder to understand. For example, TIMESTAMP is an unreserved keyword, so you can do things like:

create table t ( timestamp timestamp );

select * from t
where  timestamp > timestamp'2025-01-01 00:00:00';

I certainly find this hard to follow.

Applications can work fine if existing names clash with new keywords though. In fact you're using one right now!

Ask TOM was built before the TIMESTAMP data type was added to Oracle Database - so coincidentally has a column named TIMESTAMP!

Rating

  (2 ratings)

Comments

V$KEYWORDS?

mathguy, January 10, 2025 - 10:57 pm UTC

I am just guessing that Dmitriy's question is much simpler.

The view includes all keywords, both those that are reserved and those that are not. The view name is the one causing confusion; V$KEYWORDS would make much more sense. I guess there may be historical (and backward compatibility) reasons for the confusing name. (Perhaps the documentation is confusing too, by not explaining what I just did; I didn't check.)
Connor McDonald
January 13, 2025 - 4:44 am UTC

There is plenty of nuance when it comes to "reserved" words


A reader, January 14, 2025 - 11:17 am UTC

Thank you for the answer.
I am still not sure, which is true: you did not fully understand my question, or I did not fully understand your answer.
So, maybe, I will explain how I came to it.
I wanted to create a type with a field named VALUE, but Oracle SQL Developer highlighted that word. This usually means that it is a reserved word. So I have checked the view V$RESERVED_WORDS and saw, that the word VALUE is there, but has 'N' in all columns. This seemed confusing. They are listed in the view with reserved words, but detailed information shows they are "not reserved". I created the type and some code to work with it, and it works well (in my test examples). Another example is the keyword ID, which is also mentioned in the view. This word is often used as a name of primary key column. This code also works:

create table t (id number);

insert into t values (1);

declare
  id number;
begin
  select id into id from t;
end;
/


So, when I asked "What is the risk of using these words as object names or as variables in PL/SQL?" I asked about keywords like ID or VALUE - words, which has 'N' values in all columns in the view V$RESERVED_WORDS.
The video in the comment above partially answers this question: COUNT is another example of a word, which seems available for use.
So, as I understand, these "not reserved" words may be already used somewhere, and it is better to avoid them, even if nothing breaks immediately.
Chris Saxon
January 14, 2025 - 6:43 pm UTC

As mathguy noted the view name is misleading - these are keywords, not reserved words. Some of these keywords are reserved words. The flags on v$reserved_words will tell you this.

So, as I understand, these "not reserved" words may be already used somewhere, and it is better to avoid them, even if nothing breaks immediately.

Correct.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.