Usage of Reserved words in object naming

Today I have been assigned to do some R&D on using Reserve words as column name or object names. When we need to use reserve words in our database when we have to use double quotes with the column/object names.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL>
SQL>  create table demo (NUMBER number);
create table demo (NUMBER number)
*
ERROR at line 1:
ORA-00904: : invalid identifier


SQL> create table demo ("NUMBER" number);

Table created.

SQL> select NUMBER FROM DEMO;
select NUMBER FROM DEMO
*
ERROR at line 1:
ORA-00936: missing expression


SQL> select "NUMBER" FROM DEMO;

no rows selected

SQL>
SQL> insert into demo (NUMBER) values (1)
2  /
insert into demo (NUMBER) values (1)
*
ERROR at line 1:
ORA-00928: missing SELECT keyword


SQL> insert into demo ("number") values (1)
2  /
insert into demo ("number") values (1)
*
ERROR at line 1:
ORA-00904: "number": invalid identifier


SQL> insert into demo ("NUMBER") values (1)
2  /

1 row created.

SQL>

Moreover if we will use reserve words for column/table names in then we all have to support the reserve words in our ETL scripts and reporting tools also.

We can query all the reserve words with v$reserved_words dynamic view. Depending on the version oracle has different reserve words.