Solved

FRM-40509 Oracle unable to update record

Posted on 2004-04-07
8
10,061 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
8 Comments
 
LVL 2

Expert Comment

by:n4nazim
Comment Utility
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á
Comment Utility
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
Comment Utility
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
 
LVL 5

Expert Comment

by:KarcOrigin
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 8

Expert Comment

by:baonguyen1
Comment Utility
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 47

Expert Comment

by:schwertner
Comment Utility
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 47

Accepted Solution

by:
schwertner earned 250 total points
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now