Error Trying To Rename Columns With Space In Oracle Table. Error - Sql Error : Ora- 00946 : Missing To Keyword

I have table named employee. In which , I wish to alter the column name from Employee Name to Employee_Name (note that the existing column name has a space!) When I use the followi

Solution 1:

Since Oracle doesn't know the space is part of the column name, you have to enclose it in quotes to make sure it recognizes it correctly:

Altertable employee rename column "Employee Name" to Employee_Name

Solution 2:

I wish to alter the column name from 'Employee Name' to 'Employee_Name'

The problem lies in the fact that you used quoted identifier for column name while creating the table.

From the docs,

Database Object Naming Rules

Every database object has a name. In a SQL statement, you represent the name of an object with a quoted identifier or a nonquoted identifier.

• 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.

• A nonquoted identifier is not surrounded by any punctuation.

You need to use double quotation marks around the existing column name to fix the problem:

SQL>CREATETABLE employee("employee name" VARCHAR2(10));

Table created.

SQL>ALTERTABLE employee RENAME column "employee name" TO employee_name;

Table altered.