ORA-01446

This was tested on Oracle 11.2. First I created a table and inserted 2 identical values in it:

SQL> create table tab1 (col1 number)

  2  /

 

Table created.

 

SQL> insert into tab1 values (1)

  2  /

 

1 row created.

 

SQL> insert into tab1 values (1)

  2  /

 

1 row created.

SQL>

Then I looked at the rowids of the values I had inserted:

SQL> select rowid, col1 from tab1

  2  /

 

ROWID                    COL1

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

AAAmpvAAEAAAACLAAA          1

AAAmpvAAEAAAACLAAB          1

SQL>

Next I created a view and checked that it referred back to the underlying table and picked up the correct rowids again: 

SQL> create view view1

  2  as select col1 from tab1

  3  /

 

View created.

 

SQL> select rowid, col1 from view1

  2  /

 

ROWID                    COL1

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

AAAmpvAAEAAAACLAAA          1

AAAmpvAAEAAAACLAAB          1

SQL>

Finally I created a view to show the distinct values in the table. This worked as expected too: 

SQL> create view view2

  2  as select distinct col1 from tab1

  3  /

 

View created.

 

SQL> select col1 from view2

  2  /

 

      COL1

----------

         1

SQL> 

But when I tried to look at the rowids in this view, I got an ORA-01446 instead. The rowids in the table itself gave details of each row's physical location in the database. So when I passed these rows through a DISTINCT into a view, there were two occurrences of the value 1 in the table giving one DISTINCT value in the view. Oracle then did not know which row's rowid to associate with the value displayed: 

SQL> select rowid, col1 from view2

  2  /

select rowid, col1 from view2

       *

ERROR at line 1:

ORA-01446: cannot select ROWID from, or sample, a view

with DISTINCT, GROUP BY, etc.

 

SQL>