ORA-00960

This was tested on Oracle 11. I tried to sort on a column which appeared more than once in the SELECT list, and got an ORA-00960:

 

SQL> select username, username from dba_users

  2  order by username

  3  /

order by username

         *

ERROR at line 2:

ORA-00960: ambiguous column naming in select list

 

SQL>

 

In this situation, you need to change one or both of the names so that they no longer match:

SQL> l

  1  select username, user_id from dba_users

  2* order by username

SQL> /

 

 

USERNAME                          USER_ID

------------------------------ ----------

ANDREW                               8391

BRIAN                                8494

COLIN                                8403

Etc

 

In the next example, I wanted a list of users ordered by their creation date. Then for each user, I wanted to show the objects it owned along with their creation dates:

 

SQL> l

  1  select username, dba_users.created,

  2  object_name, dba_objects.created

  3  from dba_users, dba_objects

  4  where username = owner

  5* order by created

SQL> /

order by created

         *

ERROR at line 5:

ORA-00960: ambiguous column naming in select list

 

SQL>

 

In this case, there was a CREATED column in DBA_USERS and DBA_OBJECTS so I had to tell Oracle which one to sort on. There are two ways to do this. You can prefix the sort key with the table_name:

 

SQL> l

  1  select username, dba_users.created,

  2  object_name, dba_objects.created

  3  from dba_users, dba_objects

  4  where username = owner

  5* order by dba_users.created

SQL> /

 

USERNAME   CREATED   OBJECT_NAME CREATED

---------- --------- ----------- ---------

SYS        11-SEP-10 CON$        11-SEP-10

SYS        11-SEP-10 I_COL2      11-SEP-10

SYS        11-SEP-10 I_USER#     11-SEP-10

SYS        11-SEP-10 C_TS#       11-SEP-10

SYS        11-SEP-10 I_OBJ#      11-SEP-10

Etc

 

Alternatively, you can use an alias:

 

SQL> l

  1  select username, x.created,

  2  object_name, y.created

  3  from dba_users x, dba_objects y

  4  where username = owner

  5* order by x.created

SQL> /

 

USERNAME   CREATED   OBJECT_NAME CREATED

---------- --------- ----------- ---------

SYS        11-SEP-10 CON$        11-SEP-10

SYS        11-SEP-10 I_COL2      11-SEP-10

SYS        11-SEP-10 I_USER#     11-SEP-10

SYS        11-SEP-10 C_TS#       11-SEP-10

SYS        11-SEP-10 I_OBJ#      11-SEP-10

Etc