 Problem with updating a view. FRM-40501. [message #119926] |
Mon, 16 May 2005 14:14  |
sweetness
Messages: 10
Registered: May 2005
Location: Pasadena
|
Junior Member |
|
|
Hi everyone,
I've had problems updating a field in one of my forms (Oracle Forms 6.0) in which the field belongs to a datablock that is based on a view. I get the following error:
FRM-40501: ORACLE error: unable to reserve record for update or delete
I know that you're not able to update a view directly using SQL -- you're supposed to use an INSTEAD OF trigger -- but I can't even type anything into that field because as soon as I type it in, I get the error and the Forms runtime engine erases the character immediately.
Help!
Thanks in advance,
Joe Bautista
|
|
|
|
|
|
| Re: Problem with updating a view. FRM-40501. [message #119946 is a reply to message #119937] |
Mon, 16 May 2005 17:37  |
sweetness
Messages: 10
Registered: May 2005
Location: Pasadena
|
Junior Member |
|
|
Steve,
Thanks. We checked the permissions and, actually, the account I was using did NOT have permission. We fixed that, but I still get the error for some reason, so we're going to keep looking.
BTW, I wasn't sure what you meant by "seeded" versus "custom". We created the view from scratch based on three existing tables.
Regards,
Joe Bautista
|
|
|
|
| Re: Problem with updating a view. FRM-40501. [message #119956 is a reply to message #119926] |
Mon, 16 May 2005 19:56 |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
What is the version of your database?
David
For Oracle 8 look in sys.user_updatable_columns to see which columns the database system will permit you to change. [Same for Oracle 9 but it has extra columns.]
Digging from memory, I think that to enable the columns to be updateable that you have to have all of the fields of the respective primary keys in the 'select' part of the view. You don't need to change them, but they do have to be retrieved.
[Updated on: Mon, 16 May 2005 20:03] Report message to a moderator |
|
|
|
| Re: Problem with updating a view. FRM-40501. [message #120088 is a reply to message #119956] |
Tue, 17 May 2005 14:06 |
sweetness
Messages: 10
Registered: May 2005
Location: Pasadena
|
Junior Member |
|
|
Sorry. I forgot to mention that we added an INSTEAD_OF trigger and were hoping to modify the underlying table by triggering that. We weren't trying to modify the view directly.
I also changed tactics this morning and modified my form so that the data block in question is now based based on a join instead of a view. No luck, though. I'm still getting that same error, in addition to ORA-00904: invalid identifier.
It's very confusing.
Joe
|
|
|
|
| Re: Problem with updating a view. FRM-40501. [message #120118 is a reply to message #119926] |
Tue, 17 May 2005 18:10 |
danosw
Messages: 20
Registered: May 2005
Location: California
|
Junior Member |
|
|
Forms is complaining about your ON-LOCK trigger. You need to set one or more of your columns meet to be set to primary key = YES.
What is the blocks Key_mode? Lock_mode?
You can use a view to update data if those columns are updateable.
In addition, I would use ON-INSERT, ON-UPDATE, and ON-DELETE forms triggers instead of using the INSTEAD OF database trigger.
|
|
|
|
| Re: Problem with updating a view. FRM-40501. [message #120119 is a reply to message #119946] |
Tue, 17 May 2005 18:14 |
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
|
Senior Member |
|
|
| sweetness wrote on Mon, 16 May 2005 18:37 |
Steve,
Thanks. We checked the permissions and, actually, the account I was using did NOT have permission. We fixed that, but I still get the error for some reason, so we're going to keep looking.
BTW, I wasn't sure what you meant by "seeded" versus "custom". We created the view from scratch based on three existing tables.
Regards,
Joe Bautista
|
Joe -
I was referring to the out of the box views that Oracle Apps uses when I said seeded view. Wasn't sure of your environment.
Good stuff Dano.
Steve
|
|
|
|
| Re: Problem with updating a view. FRM-40501. [message #416860 is a reply to message #119926] |
Tue, 04 August 2009 16:56 |
slante9
Messages: 1
Registered: August 2009
|
Junior Member |
|
|
In Oracle Forms when using a view or from clause query as the data source, isn't it necessary to use the "On" triggers to do DML, else you get an FRM-40501? On triggers =
On-Lock
On-Insert
On-Update
On-Delete
Where developer must supply the DML.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
