1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle Reserved keywords

Discussion in 'SQL PL/SQL' started by Arju, Oct 11, 2008.

  1. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    Reserved Keywords in Oracle

    Let me start this thread with some examples.
    Code (Text):

    SQL> create table or(a number);
    create table or(a number)
                 *
    ERROR at line 1:
    ORA-00903: invalid table name


    SQL> select * from all;
    select * from all
                  *
    ERROR at line 1:
    ORA-00903: invalid table name

    SQL> create table test_col(user varchar2(10));
    create table test_col(user varchar2(10))
                          *
    ERROR at line 1:
    ORA-00904: : invalid identifier
    All of these example fail to create table. It is because that oracle has several reserved words and these reserved keywords can't be used in different places like naming in identifier or in attribute.

    The v$reserved_words view show a list of reserved keywords in oracle.
    Code (Text):
    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 RESERVED field value Y indicates that the corresponding row is always reserved and hence we can't name in anywhere in oracle as it's name.

    If the RESERVED field value is N then it indicates that the keyword is not always reserved and hence can be used based on RES_TYPE, RES_ATTR, RES_SEMI value.

    Now have a look at the value with these keywords.

    HTML:
    SQL> select * from v$reserved_words where keyword in ('OR','ALL','USER');

    KEYWORD                            LENGTH R R R R D
    ------------------------------ ---------- - - - - -
    ALL                                     3 Y N N N N
    USER                                    4 N N N Y N
    OR                                      2 Y N N N N
    However, we can use reserved words while naming by using quotes. Like,
    Code (Text):
    SQL> create table "ALL" ( a number);

    Table created.
    And accessing of these also need quotes. Like
    Code (Text):
    SQL> desc all;
    ERROR:
    ORA-00931: missing identifier

    SQL> desc "ALL";
     Name                                      Null?    Type
     ----------------------------------------- --------
     A                                                  NUMBER
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    To Add to the Post above. There are four kinds of Reserved objects to be kept in mind:

    1. Oracle Reserved Words
    2. Oracle Keywords
    3. PL/SQL Reserved Words
    4. Oracle Reserved Namespaces

    Oracle Reserved Words have a special meaning to Oracle and they cannot be redefined to name database objects such as columns, tables, or indexes.

    Oracle Keywords also have a special meaning to Oracle but are not reserved words and so can be redefined. However, some might eventually become reserved words.

    PL/SQL Reserved Words are those that may require special treatment when used in embedded SQL statements

    Finally, Oracle Reserved Namespaces are reserved by Oracle. The function names in Oracle libraries have initial characters which are restricted to the following strings. To avoid potential name conflicts, do not use function names that begin with these characters.

    Namespace - Library
    O - OCI functions
    S - function names from SQLLIB and system-dependent libraries
    XA - external functions for XA applications only
    GEN KP L NA NC ND NL NM NR NS NT NZ TTC - UPI Internal functions
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    SQL> create table "ALL" ( a number);

    Table created.

    will work .

    Btw, this kind of table script is never encouraged though it is supported.
     
  4. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Yes of course, Arju was just pointing out the possibilities...
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Yup , I know.

    I know ARJU well ( in my another forum ) . he is an expert .

    Even i was trying to point out the pitfall behind it , so that nobody should try ( especially newbies) unless its something very urgent .
     
  6. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    The only exception is ROWID. Uppercase ROWID can't be used either even in quotes as a column name. like,
    Code (Text):
    SQL> create table test(rowid number);
    create table test(rowid number)
                      *
    ERROR at line 1:
    ORA-00904: : invalid identifier


    SQL> create table "ROWID"("ROWID" number);
    create table "ROWID"("ROWID" number)
    *
    ERROR at line 1:
    ORA-00904: "ROWID": invalid identifier


    SQL> create table test("ROWID" number);
    create table test("ROWID" number)
    *
    ERROR at line 1:
    ORA-00904: "ROWID": invalid identifier
    However in table name it can be used.
    Code (Text):
    SQL> create table "ROWID"(test number);

    Table created.
    As well as if we name it as lowercase also possible.
    Code (Text):
    SQL>  create table "ROWI"("rowid" number);

    Table created.
    Reference
    http://arjudba.blogspot.com/2008/07/schema-object-naming-rules.html