We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

FRM-40509 Oracle unable to update record

uTab
uTab asked
on
Medium Priority
12,430 Views
Last Modified: 2011-08-18
I have a view from a single table created by the user trying to perform the update. I am unable to update though. When I click Help about error I get the message ORA 01733: Virtual column not allowed here.  Help!
Comment
Watch Question

Commented:
Hi,

Pl post the view SQL and the code which gives u this error..

Rgds,
Nazim M
Helena Markováprogrammer-analyst
CERTIFIED EXPERT

Commented:
ORA-01733 virtual column not allowed here

Cause: An attempt was made to use an INSERT, UPDATE, or DELETE statement on an expression in a view.

Action: INSERT, UPDATE, or DELETE data in the base tables, instead of the view.

It seems that you want to manipulate "not a single column" in your view. So you can try to make properties of this column in your block:
insert_allowed=no
update_allowed=no
delete_allowed=no

I hope this will help you.
Hi,
Just check if your view or the object is readonly. The error message could be misleading. Here it shows as if the error was because the alias of the column is used.

Just check it.
Hi,
If your view is not read only then just check if you are trying to update the columns that are based on the arithmetic expression.
I hope it helps.
Thx
This error can happen if  when you try to update or insert on a Forms database block,
which is built against a view. This error occurs because you have created a virtual column in your view.

This is the expected product behaviour.
 
Example TABLE and VIEW:
 
Table PERSONNEL is
(EMPNO       NUMBER(4),
 FIRST_NAME  VARCHAR2(15),
 LAST_NAME   VARCHAR2(15),
 DEPTNO      NUMBER(2))
 
View PERSONNEL_VIEW text is as follows:
 
create view PERSONNEL_VIEW as
   select empno employee_number,
          first_name||' '||last_name employee_name,
          deptno department_number
   from   PERSONNEL;
 
The view column employee_name is a virtual column because a space mark and
two table columns are combined to create the employee_name column.
Running a form with a database block built against this view will succeed
when you query or delete records.  Inserts and updates will fail with
ORA-01733 error, because database cannot update or insert into
a virtual column.

Hope this helps
CERTIFIED EXPERT
Top Expert 2008

Commented:
You have to define INSTEAD OF type triggers on the view. These triggers will do the insert, update, delete operation on the view according the code you provide in the triggers.
CERTIFIED EXPERT
Top Expert 2008
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
You all are my heros. I had tried an insted of trigger but it was wrong.  Thank all of you who helped me out. I gave the points to schwertner because he answer was excellent and helped me get my right direction working.

Thanks again to all or you!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.