i am trying to create a table in oracle sql as shown below

create table Employee
(

S.NO NUMBER(2)  PRIMARY KEY,

NAME VARCHAR(20),

DESIGNATION VARCHAR(20),

BRANCH VARCHAR(20)
)

it shows an error "only simple column names allowed here"

please help!!

CodePudding user response：

Generally, it's a bad idea to name columns like that because it might (and will) make your code less readable.

But if you really-really-really need it, you can use quotes

create table test(
  "s.no" number
);

dbfiddle

CodePudding user response：

Almost every character can be used in an identifier if and only if you're using quotation at table definition. Let's see an example.

SQL> create table t1 ("S.NO" number, "!@#$%^&*()" varchar2(10));

Table created.

SQL> desc t1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 S.NO                                               NUMBER
 !@#$%^&*()                                         VARCHAR2(10)

From now on, you have to use quotation to wrap these weird identifiers in every statement you used.

SQL> insert into t1 ("S.NO", "!@#$%^&*()") values (1, 'abc');

1 row created.

SQL> select "!@#$%^&*()" from t1 where "S.NO" = 1;

!@#$%^&*()
----------
abc

Normally, we don't use quotation to define column or table identifier, simply because it's error prone.