OraFAQ Forum: Forms » Problem with updating a view. FRM-40501.

Home » Developer & Programmer » Forms » Problem with updating a view. FRM-40501.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: Problem with updating a view. FRM-40501. [message #119937 is a reply to message #119926]

Mon, 16 May 2005 15:50

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

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

Report message to a moderator

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

Report message to a moderator

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.

Report message to a moderator

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

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

Report message to a moderator

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.

Report message to a moderator

Re: Problem with updating a view. FRM-40501. [message #416865 is a reply to message #119926]

Tue, 04 August 2009 18:00

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

You do realise this thread is 4 years old right?

Report message to a moderator

Re: Problem with updating a view. FRM-40501. [message #417030 is a reply to message #416865]

Wed, 05 August 2009 14:35

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

So? It is still much younger than me /forum/fa/917/0/

Report message to a moderator

Re: Problem with updating a view. FRM-40501. [message #635616 is a reply to message #417030]

Thu, 02 April 2015 10:48

devmode100
Messages: 1
Registered: April 2015
Location: Vietnam

Junior Member

In the Block: Change value of "Key Mode" from "Automatic" to "None Updateable".

Report message to a moderator

Re: Problem with updating a view. FRM-40501. [message #635626 is a reply to message #635616]

Fri, 03 April 2015 08:01

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.

Report message to a moderator

Re: Problem with updating a view. FRM-40501. [message #673610 is a reply to message #119926]

Mon, 26 November 2018 22:54

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.

Report message to a moderator

Previous Topic:	Creating dynamic table based on Excel data
Next Topic:	access denied (java.lang.RuntimePermission loadLibrary.jacob) WEBUTIL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 10:45:13 CDT 2025