Wednesday, May 13, 2009

Reserved Words as Oracle Column Names

I saw a post on the web asking if one could create an Oracle table in which a column name was an Oracle reserved word like "TO" or "FROM". Now, not that I would do this, but it intrigued me, so I tried:

SQL> create table bogus (to varchar2(2));
create table bogus (to varchar2(2))
*
ERROR at line 1:
ORA-00904: : invalid identifier


SQL> CREATE TABLE bogus AS
2 SELECT dummy "TO", dummy "FROM" FROM DUAL;

Table created.

SQL> desc bogus
Name Null? Type
----- ----- --------------------------
TO VARCHAR2(1)
FROM VARCHAR2(1)

SQL> select * from bogus;

TO FROM
-- ----
X X

SQL>


I started up SQL Developer to see what the CREATE TABLE statement would be for the BOGUS table:

CREATE TABLE "BOGUS"
("TO" VARCHAR2(1),
"FROM" VARCHAR2(1));


To select from the BOGUS table:

SQL> select "TO", "FROM" from bogus;

TO FROM
-- ----
X X

SQL>


There it is.

1 comment:

Delfino N. said...

The problem is when you have something like this:

SQL> create table t ("TO" number, "to" number, "To" number, "tO" number);

Table created.

SQL> desc t;
Name Null? Type
----------------------------------------- -------- ----------------------------
TO NUMBER
to NUMBER
To NUMBER
tO NUMBER


at the end you should always use the quotes to reference the columns.