Friday, September 14, 2012

ORA-00957 and ORA-00918

This was tested on Oracle 11.2. You cannot have more than 1 column in a table or view with a given name:

SQL> create table andrew
  2  (col1 number, col1 varchar2(10))
  3  /
(col1 number, col1 varchar2(10))
              *
ERROR at line 2:
ORA-00957: duplicate column name

SQL>

So, if you are only selecting from 1 table, Oracle always knows where to get the column(s) from. However, if you join 2 (or more) tables or views, this is no longer true:

SQL> select sid, value
  2  from v$mystat, v$sysstat
  3  where statistic# = statistic#
  4  and name = 'CPU used by this session'
  5  /
where statistic# = statistic#
                   *
ERROR at line 3:
ORA-00918: column ambiguously defined

SQL>

To stop this happening, you need to tell Oracle which table or view to get each column from. You can do this by putting the table or view name before the column name as follows:

SQL> l
  1  select sid, v$mystat.value
  2  from v$mystat, v$sysstat
  3  where v$mystat.statistic# = v$sysstat.statistic#
  4* and name = 'CPU used by this session'
SQL> /

       SID      VALUE
---------- ----------
       684         37

SQL>

Alternatively, you can give them aliases like this:

SQL> l
  1  select sid, a.value
  2  from v$mystat a, v$sysstat b
  3  where a.statistic# = b.statistic#
  4* and name = 'CPU used by this session'
SQL> /

       SID      VALUE
---------- ----------
       684         41

SQL>

You need to do this for value and statistic# because they appear in both v$mystat and v$sysstat. However, you do not need to do it for sid because it only appears in v$mystat:

SQL> desc v$mystat
Name                       Null?    Type
-------------------------- -------- ------------------
SID                                 NUMBER
STATISTIC#                          NUMBER
VALUE                               NUMBER

SQL> desc v$sysstat
Name                       Null?    Type
-------------------------- -------- ------------------
STATISTIC#                          NUMBER
NAME                                VARCHAR2(64)
CLASS                               NUMBER
VALUE                               NUMBER
STAT_ID                             NUMBER

SQL>

No comments: