Home » Developer & Programmer » Forms » Problem with updating a view. FRM-40501.
icon1.gif  Problem with updating a view. FRM-40501. [message #119926] Mon, 16 May 2005 14:14 Go to next message
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 #119937 is a reply to message #119926] Mon, 16 May 2005 15:50 Go to previous messageGo to next message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
Is this a privelage issue? Sounds like it to me. You may have to grant update/delete/insert privelages on the view.

HTH,
Steve

Also - Is this a seeded view or a custom view that you created?

[Updated on: Mon, 16 May 2005 15:50]

Report message to a moderator

Re: Problem with updating a view. FRM-40501. [message #119946 is a reply to message #119937] Mon, 16 May 2005 17:37 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: Problem with updating a view. FRM-40501. [message #416865 is a reply to message #119926] Tue, 04 August 2009 18:00 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
You do realise this thread is 4 years old right?
Re: Problem with updating a view. FRM-40501. [message #417030 is a reply to message #416865] Wed, 05 August 2009 14:35 Go to previous messageGo to next message
Littlefoot
Messages: 21805
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So? It is still much younger than me /forum/fa/917/0/
Re: Problem with updating a view. FRM-40501. [message #635616 is a reply to message #417030] Thu, 02 April 2015 10:48 Go to previous messageGo to next message
devmode100
Messages: 1
Registered: April 2015
Location: Vietnam
Junior Member
In the Block: Change value of "Key Mode" from "Automatic" to "None Updateable".
Re: Problem with updating a view. FRM-40501. [message #635626 is a reply to message #635616] Fri, 03 April 2015 08:01 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
And to one-up cookiemonster, you sir have opened up thread that has been dormant for 6 years.
Re: Problem with updating a view. FRM-40501. [message #673610 is a reply to message #119926] Mon, 26 November 2018 22:54 Go to previous message
tolgyesi
Messages: 1
Registered: June 2008
Location: Budapest
Junior Member
I had the same problem. A view was based on two tables joined. FORMS could not reserve the records until I noticed that one of the tables had no primary key. I built it and everything went fine.
Previous Topic: Creating dynamic table based on Excel data
Next Topic: access denied (java.lang.RuntimePermission loadLibrary.jacob) WEBUTIL
Goto Forum:
  


Current Time: Tue Mar 19 00:39:48 CDT 2024