Wednesday, September 21, 2011

Renaming Columns

Tested on an Oracle 9 database. You can rename columns with the alter table command:

SQL> create table with_columns_to_rename
  2  (old_name number)
  3  /

Table created.

SQL> desc with_columns_to_rename
Name                    Null?    Type
----------------------- -------- ----------------
OLD_NAME                        NUMBER

SQL> alter table with_columns_to_rename
  2  rename column old_name to new_name
  3  /

Table altered.

SQL> describe with_columns_to_rename
Name                    Null?    Type
----------------------- -------- ----------------
NEW_NAME                        NUMBER

SQL> 


But if the column to be renamed does not exist, you will get an ORA-00904:

SQL> alter table with_columns_to_rename
  2  rename column missing to found
  3  /
rename column missing to found
              *
ERROR at line 2:
ORA-00904: "MISSING": invalid identifier

SQL>


This has nothing to do with this post, but Oracle allows you to do desc, descr, descri, describ or describe to show the layout of a table:

SQL> descri with_columns_to_rename
Name                    Null?    Type
----------------------- -------- ----------------
NEW_NAME                        NUMBER

SQL>

No comments: