Monday, October 07, 2013

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

1 comment:

Nigel Thomas said...

Or you can just order by the column number: ORDER BY 2