Oracle Tips and Tricks — David Fitzjarrell

December 10, 2008

We Have Reservations

Filed under: General — dfitzjarrell @ 19:55

A question was posed to recently which stated:

“can we have a row named final in oracle table”

To answer that (presuming it was column, and not row, intended in the question) one must make a trip to V$RESERVED_WORDS:

SQL> desc v$reserved_words
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 KEYWORD                                            VARCHAR2(30)
 LENGTH                                             NUMBER
 RESERVED                                           VARCHAR2(1)
 RES_TYPE                                           VARCHAR2(1)
 RES_ATTR                                           VARCHAR2(1)
 RES_SEMI                                           VARCHAR2(1)
 DUPLICATE                                          VARCHAR2(1)

The two columns of interest are KEYWORD and RESERVED. Let’s look for ‘FINAL’ in the view and see whether or not it’s reserved:

SQL> select keyword, reserved
  2  from v$reserved_words
  3  where keyword like 'F%'
  4  order by 1;
KEYWORD                        R
------------------------------ -
FACT                           N
FAILED                         N
FAILGROUP                      N
FALSE                          N
FAST                           N
FBTSCAN                        N
FIC_CIV                        N
FIC_PIV                        N
FILE                           N
FILTER                         N
KEYWORD                        R
------------------------------ -
FINAL                          N
FINE                           N
FINISH                         N
FIRST                          N
FIRST_ROWS                     N
FLAGGER                        N
FLASHBACK                      N
FLOAT                          Y
FLOB                           N
FLUSH                          N
FOLLOWING                      N
KEYWORD                        R
------------------------------ -
FOR                            Y
FORCE                          N
FOREIGN                        N
FREELIST                       N
FREELISTS                      N
FREEPOOLS                      N
FRESH                          N
FROM                           Y
FULL                           N
FUNCTION                       N
KEYWORD                        R
------------------------------ -
FUNCTIONS                      N
34 rows selected.

It’s in the list of reserved words, but it’s not actually reserved, meaning you CAN use it if you REALLY REALLY want to, but it’s recommended that you don’t. I wouldn’t use it. But, that’s the way I think.

What other words shouldn’t you use for column names/table names/view names/constraint names/sequence names/type names/…? Simply look at the list provided in, yes, V$RESERVED_WORDS. There are 1142 of them in 10gR2, 1733 in 11gR1; they’re listed for good reason, in my opinion, and the list should be heeded. And, yes, many of the words are not actually reserved (as indicated by the N in RESERVED) but it’s still a really good idea to not use them as, some day, Oracle may decide to actually reserve them and then your scripts won’t run:

SQL> create table from(
  2  final varchar2(400)
  3  );
create table from(
ERROR at line 1:
ORA-00903: invalid table name


And that would be a shame.

Reservations, anyone?



  1. David,for those who want to have really fun with reserved words, quotes and lowercase can increase the fun:SQL> create table "from"( final varchar2(400) ) ;Table created.nice posts, btw!

    Comment by Martin Berger — June 10, 2009 @ 18:42 | Reply

  2. really fun and unbelievable thanks for information martin

    Comment by Rajeshkumar Govindarajan — August 8, 2009 @ 10:29 | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at

%d bloggers like this: