Monday, April 28, 2014

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>

No comments: