| Updating Base tables of a View [message #162388] |
Fri, 10 March 2006 00:29  |
nirmalnarayan
Messages: 261
Registered: April 2005
Location: India
|
Senior Member |
|
|
While trying to update the base tables of a view which is based on 6 tables i am getting the following error. How to overcome this error and update the values ? I am using Oracle 10g
ORA-01779: cannot modify a column which maps to a non key-preserved table
Nirmal
|
|
|
|
|
|
| Re: Updating Base tables of a View [message #162421 is a reply to message #162391] |
Fri, 10 March 2006 02:48  |
nirmalnarayan
Messages: 261
Registered: April 2005
Location: India
|
Senior Member |
|
|
Let me explain how this problem arised. I am posting this as it is related to Both Forms and as well as Database.
I am working on Forms10g , I have a form which as said above is based on a multi-table join view. I have a multi-record datablock, the datablock is based on the above said view.
I am handling insert into this tables, update into this tables etc. through form triggers like ON-INSERT , ON-UPDATE, to the individual tables.
I have LOV's associated with the text fields. On selection of a LOV i am populating another LOV associated with the second text field, and if any value is already there in the second text box i will clear it out on selection of a value from the first LOV on first text box.
Here is where i getting the problem. While selecting a value from the LOV from the second row onwards in the block ( i am not getting this on 1st row of the block) i am getting the following error.
I am just selecting the value from LOV and returning the value to the first text field, also i will clear the value in second text field, as the second LOV will be refreshed by this time according to the first LOV. Problem is all the fields in the block is based on this view, and while trying to clear an item (on selection of a value from first LOV) or trying to change the value of an item by selecting from the first LOV(in the first text field) i am getting this error. I am the only person using this form, still this error message reads as
'Record has been updated by another user, Requery to change' see the image below
How to overcome this issue keeping the given situation ( The Datablock has to be based on this View itself)
Thanks
Nirmal
-
Attachment: Fmrerror.bmp
(Size: 208.04KB, Downloaded 5185 times)
[Updated on: Fri, 10 March 2006 03:01] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Updating Base tables of a View [message #162454 is a reply to message #162452] |
Fri, 10 March 2006 05:48 |
nirmalnarayan
Messages: 261
Registered: April 2005
Location: India
|
Senior Member |
|
|
Even after making it 'Yes' the problem exists. and i am not even modifying the data in the Database, i am just changing the existing value by selecting alternate value from the LOV, this is happening in Front end only, still i am getting the same problem.
Thanks
Nirmal
|
|
|
|
|
|
| Re: Updating Base tables of a View [message #162843 is a reply to message #162461] |
Mon, 13 March 2006 17:43 |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
I assume that you have 'googled' your error message 'ORA-01779: cannot modify a column which maps to a non key-preserved table' and seen the articles like http://www.jlcomp.demon.co.uk/faq/joinview.html. The common wisdom is to modify your tables and views so that you have primary keys on each table and that ALL the fields of the primary keys are part of the join condition in the view. This is so that when you look at the 'user_updatable_columns' you will see that all the fields of your view are defined as 'updatable'. Note that
The following is from the 10g documentation at http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8004.htm
| Quote: |
Notes on Updatable
Views The following notes apply to updatable views:
An updatable view is one you can use to insert, update, or delete base table rows. You can create a view to be inherently updatable, or you can create an INSTEAD OF trigger on any view to make it updatable.
To learn whether and in what ways the columns of an inherently updatable view can be modified, query the USER_UPDATABLE_COLUMNS data dictionary view. The information displayed by this view is meaningful only for inherently updatable views. For a view to be inherently updatable, the following conditions must be met:
Each column in the view must map to a column of a single table. For example, if a view column maps to the output of a TABLE clause (an unnested collection), then the view is not inherently updatable.
The view must not contain any of the following constructs:
A set operator
A DISTINCT operator
An aggregate or analytic function
A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
A collection expression in a SELECT list
A subquery in a SELECT list
A subquery designated WITH READ ONLY
Joins, with some exceptions, as documented in Oracle Database Administrator's Guide
In addition, if an inherently updatable view contains pseudocolumns or expressions, then you cannot update base table rows with an UPDATE statement that refers to any of these pseudocolumns or expressions.
If you want a join view to be updatable, then all of the following conditions must be true:
The DML statement must affect only one table underlying the join.
For an INSERT statement, the view must not be created WITH CHECK OPTION, and all columns into which values are inserted must come from a key-preserved table. A key-preserved table is one for which every primary key or unique key value in the base table is also unique in the join view.
For an UPDATE statement, the view must not be created WITH CHECK OPTION, and all columns updated must be extracted from a key-preserved table.
For a DELETE statement, if the join results in more than one key-preserved table, then Oracle Database deletes from the first table named in the FROM clause, whether or not the view was created WITH CHECK OPTION.
|
Alternatively, and this goes against nearly everything I have posted in this forum, instead of basing your block on the view, have you considered making the block, a non-database block, and populating it via a cursor. See the reference http://www.orafaq.com/forum/m/605/67467/?srch=populate+block+cursor+create_record#msg_605 and my feelings about this type of block usage http://www.orafaq.com/forum/m/134237/67467/?srch=populate+block+cursor+create_record#msg_134237
David
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
