Oracle Tips and Tricks — David Fitzjarrell

December 10, 2008

We Have Reservations

Filed under: General — dfitzjarrell @ 19:55

A question was posed to google.com 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)
 
SQL>

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
FAILED_LOGIN_ATTEMPTS          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
FORCE_XML_QUERY_REWRITE        N
FOREIGN                        N
FREELIST                       N
FREELISTS                      N
FREEPOOLS                      N
FRESH                          N
FROM                           Y
FULL                           N
FUNCTION                       N
 
KEYWORD                        R
------------------------------ -
FUNCTIONS                      N
 
34 rows selected.
 
SQL>

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

SQL>

And that would be a shame.

Reservations, anyone?

Advertisements

2 Comments »

  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:

WordPress.com Logo

You are commenting using your WordPress.com 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

Blog at WordPress.com.

%d bloggers like this: