Solved

FRM-40509 Oracle unable to update record

Posted on 2004-04-07
8
10,417 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!
0
Comment
Question by:uTab
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 2

Expert Comment

by:n4nazim
ID: 10780964
Hi,

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

Rgds,
Nazim M
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10781032
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.
0
 
LVL 5

Expert Comment

by:KarcOrigin
ID: 10781246
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.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 5

Expert Comment

by:KarcOrigin
ID: 10781288
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
0
 
LVL 8

Expert Comment

by:baonguyen1
ID: 10781413
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
0
 
LVL 48

Expert Comment

by:schwertner
ID: 10781468
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.
0
 
LVL 48

Accepted Solution

by:
schwertner earned 250 total points
ID: 10781482
If you want to INSERT, UPDATE, DELETE records based on views you have two ways. The FIRST one is to provide DB triggers of type "Instead of" to do these actions.

Example:
create or replace view CAND_PGRS_VIEW
as select
cp.id, cp.to_employee, cp.progress_type, cp.planned_on_date,
cp.held_on_date, cp.result, cp.remark,
cpt.name, cpt.place, cpt.in_use
from CANDIDATE_PROGRESS cp, CANDIDATE_PROGRESS_TYPE cpt
where cp.progress_type = cpt.id;


CREATE OR REPLACE TRIGGER candidate_progress_update
INSTEAD OF UPDATE  ON cand_pgrs_view
DECLARE
 duplicate_info EXCEPTION;
 PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
 UPDATE staff.candidate_progress
   SET  planned_on_date = :new.planned_on_date,
      held_on_date    = :new.held_on_date,
      result          = :new.result,
      remark          = :new.remark
 WHERE id = :new.id;
END candidate_progress_update;
/


CREATE OR REPLACE TRIGGER candidate_progress_input
INSTEAD OF INSERT  ON cand_pgrs_view
DECLARE
 duplicate_info EXCEPTION;
 PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
 INSERT INTO staff.candidate_progress
           (id, to_employee, progress_type, planned_on_date,
          held_on_date, result, remark )
 VALUES  (:new.id, :new.to_employee, :new.progress_type, :new.planned_on_date,
          :new.held_on_date, :new.result, :new.remark );
EXCEPTION
 WHEN duplicate_info THEN
 RAISE_APPLICATION_ERROR (
  num => -21107,
  msg => 'Duplicated Candidate Progress Record');
END candidate_progress_input;
/

In Forms you have a SECOND way. The functionality of the "Instead of" triggers may be implemented in ON-INSERT, ON-UPDATE triggers on block level which replace the standard Forms functionality at commot phase.

Good luck!
0
 

Author Comment

by:uTab
ID: 10786020
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!
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question