Peter Teoh wrote:
I executed the following query: select rowid, table_name from dba_tables where table_name = 'r' * ERROR at line 1:ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved tableCheck the internet but provide no reason, leads. Neither does "oerr" help - nothing mentioned for cause and action.
Sorry for the noise...I am beginning to answer my own questions.......According to http://igor.gold.ac.uk/oracle/9i/server.920/a96521/views.htm, only key-preserved table have the concept of rowid, correct?
And why only key-preserved table's view can have rowid? This is because only the kp-table can be uniquely identified by row, but not the one without key-preserved, in the view composing of kp and non-kp tables.
What is a key-preserved table then? Essentially, my interpretation is that kp-table (inside the definition of a view) allows queries on the view to be mapped back one-to-one to that specific table. So the example in the above link mentioned that emp is a key-preserved table, but not dept. So update to column in emp_dept that correspond to ANY columns from emp is allowed, but not dept. Correct? So the existence of this kp-table inside a view is called "updateable join view", correct?
dept is not a kp-table in the emp_dept view. but it is a kp-table in the following view:
SQL> create view dept1v as select * from dept; View created. SQL> select rowid from dept1v; AAADpsAAFAAAC+9AAASo "key-preserved table" must always be followed by "with respect to a particular viewname". This is the key concept not mentioned clearly, and another is that there is a one-to-one mapping involved. Are my analysis correct?
Please enlighten.....:-). -- //www.freelists.org/webpage/oracle-l