CODEWITHSUNDEEP
oracle-databaseoracle9i
Bhavesh
Bhavesh

Reputation: 4677

Something unknown happened with a simple CREATE statement in Oracle 9i

One day, I accidently issued a CREATE statement on SQL prompt in Oracle 9i with all columns enclosing within double quotation marks as below.

 CREATE TABLE emp("emp_id" VARCHAR2(6) primary key, 

"emp_name" VARCHAR2(30) not null, "salary" NUMBER);

instead of issuing it as the one mentioned below without enclosing the column names within quotation marks as usual.

CREATE TABLE emp(emp_id VARCHAR2(6) primary key, 

emp_name VARCHAR2(30) not null, salary NUMBER);

This simple query (first mentioned) worked just fine with no problem at all and the emp table was created successfully just then I have created three rows into this table with the following INSERT command.

INSERT INTO emp VALUES("E0001", "Henery", 50000);
INSERT INTO emp VALUES("E0002", "Alex", 65000);
INSERT INTO emp VALUES("E0003", "Peter", 70000);

Three rows created successfully into the emp table. I then executed a SELECT statement to verify whether they were created or not and found that they were indeed created.

SELECT * FROM emp;

but when I executed the SELECT statement like the one below

SELECT emp_id, emp_name, salary FROM emp;

I made sure that though, I had used the same column names as they were actually in the emp table, Oracle issued an error indicating that "Such columns don't exist."

Upvotes: 1

Views: 299

Answers (1)

Jon Heller
Jon Heller

Reputation: 36832

From the SQL Language Reference:

"A quoted identifier begins and ends with double quotation marks ("). If you name a schema object using a quoted identifier, then you must use the double quotation marks whenever you refer to that object."

Upvotes: 3

Related Questions