CODEWITHSUNDEEP
sqloracle-databaseoracle10g
user1401321
user1401321

Reputation: 111

Rename column with reserved word as column name; Works on one, fails on another

I'm renaming two columns in two tables, both of which are named LEVEL, which is a reserved word.

What is confusing me greatly is that I am running an identical command on both these tables, but only one of them is failing. Here are the commands: 

--this works
ALTER TABLE notificationsubscriptions RENAME COLUMN "LEVEL" TO nlevel;
--this doesn't
ALTER TABLE notifications RENAME COLUMN "LEVEL" TO nlevel;

The second statement results in the following error: 

Error starting at line : 9 in command -
ALTER TABLE notifications RENAME COLUMN "LEVEL" TO nlevel
Error report -
SQL Error: ORA-00900: invalid SQL statement
00900. 00000 -  "invalid SQL statement"
*Cause:    
*Action:

Both these columns are of the same datatype (number). I am baffled - Why would this command work for one of these tables but not the other?

The database is running Oracle 10.2.

Upvotes: 0

Views: 485

Answers (1)

qwerty qwerty
qwerty qwerty

Reputation: 36

Try to remove any links that exist on this column from indexes and/or foreign key constraints

  • Drop indexes
  • Rename column
  • Create indexes with the new name of column

Upvotes: 1

Related Questions