FRM-40509 Oracle unable to update record

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!
uTabAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
schwertnerConnect With a Mentor Commented:
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
 
n4nazimCommented:
Hi,

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

Rgds,
Nazim M
0
 
Helena Markováprogrammer-analystCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
KarcOriginCommented:
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
 
KarcOriginCommented:
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
 
baonguyen1Commented:
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
 
schwertnerCommented:
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
 
uTabAuthor 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!
0
All Courses

From novice to tech pro — start learning today.