ORA-00903: Oracle Database Reserved Words

I will start this post with some experiment.

1)Create a Table named ACCESS but failed- ACCESS is Reserved Keyword.
SQL> CREATE TABLE ACCESS ( A NUMBER);
CREATE TABLE ACCESS ( A NUMBER)
*
ERROR at line 1:
ORA-00903: invalid table name

2)Select from a Table failed with ORA-00903:- Table spelling is not correct.
SQL> select * from as;
select * from as
*
ERROR at line 1:
ORA-00903: invalid table name

3)Create Table with named 1table failed with ORA-00903:- Invalid name specification.
SQL> create table 1table ( a number);
create table 1table ( a number)
*
ERROR at line 1:
ORA-00903: invalid table name

In above three cases we got the error ORA-00903. The summary of the expriment suggest that while table creation if I get ORA-00903 error then we have to look at,

•The specified table or cluster name is valid. The valid table name must be less than or equal to 30 characters.

•The table or cluster name must begin with a letter and may contain only alphanumeric characters and the special characters $, _, and #.

•The table name cannot be a reserved word.

Hopefully we already understood first two scenario. The third one need an special attention like which keywords are reserved in oracle? The reserved keyword can be found by querying V$RESERVED_WORDS data dictionary view.

We can see the always reserved keywords by following query,
select keyword from v$reserved_words where reserved='Y';

Let's see the table.
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)
•A value of column reserved Y means that the keyword cannot be used as an identifier. A value of N means that it is not reserved and hence can be used as an identifier.

•A value of column RES_TYPE Y means that the keyword cannot be used as a type name. A value of N means that it can be used as a type name.

•A value of column RES_ATTR Y means that the keyword cannot be used as an attribute name. A value of N means that it is not reserved as an attribute name.

•A value of column RES_SEMI 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.
In order words Y means the keyword is always reserved or N means it is reserved only for particular uses.

In order to know a complete list of oracle always reserved keywords you can query,
SQL> select keyword from v$reserved_words where reserved='Y' order by keyword;

KEYWORD
------------------------------
!
&
(
)
*
+
,
-
.
/
:
<=>
@
ALL
ALTER
AND
ANY
AS
ASC
BETWEEN
BY
CHAR
CHECK
CLUSTER
COMPRESS
CONNECT
CREATE
DATE
DECIMAL
DEFAULT
DELETE
DESC
DISTINCT
DROP
ELSE
EXCLUSIVE
EXISTS
FLOAT
FOR
FROM
GRANT
GROUP
HAVING
IDENTIFIED
IN
INDEX
INSERT
INTEGER
INTERSECT
INTO
IS
LIKE
LOCK
LONG
MINUS
MODE
NOCOMPRESS
NOT
NOWAIT
NULL
NUMBER
OF
ON
OPTION
OR
ORDER
PCTFREE
PRIOR
PUBLIC
RAW
RENAME
RESOURCE
REVOKE
SELECT
SET
SHARE
SIZE
SMALLINT
START
SYNONYM
TABLE
THEN
TO
TRIGGER
UNION
UNIQUE
UPDATE
VALUES
VARCHAR
VARCHAR2
VIEW
WHERE
WITH
[
]
^
|

99 rows selected.

See here ACCESS keyword does not appear beacuse ACCESS can't be used as an identifier.

SQL> select keyword,reserved from v$reserved_words where keyword='ACCESS';

KEYWORD R
------------------------------ -
ACCESS N

Related Documents:
Troubleshooting ORA-00942: ORA-04043: