Updating a View

Question

I have the below view and I am trying to update the view. The error : "virtual column not allowed here" is being displayed. How can I arrange this?

Thanks Keith Spiteri

View:

CREATE OR REPLACE VIEW FilmDetailsView
   (Film_Name, Actor_FullName, Hall_Number, Date_Time)
AS SELECT flm.film_name, actor.actor_name || ' ' || actor.actor_surname, 
   hall.cinemahall_number, schedule.schedule_date
FROM film flm 
JOIN movieschedule schedule
ON (flm.film_id = schedule.schedule_filmid)
JOIN cinemahall hall
ON (schedule.schedule_hallid = hall.cinemahall_id)
JOIN FilmActor filmactor
ON (flm.film_id = filmactor.filmactor_filmid)
JOIN Actor actor
ON (actor.actor_id = filmactor.filmactor_actorid);

The Update:

UPDATE FILMDETAILSVIEW
SET ACTOR_FULLNAME = 'a'
WHERE HALL_NUMBER = 1;

The Error:

Error starting at line 312 in command:
UPDATE FILMDETAILSVIEW
SET ACTOR_FULLNAME = 'a'
WHERE HALL_NUMBER = 1
Error at Command Line:313 Column:4
Error report:
SQL Error: ORA-01733: virtual column not allowed here
01733. 00000 -  "virtual column not allowed here"
*Cause:    
*Action:

Answer 1

A view is just a stored query -- when you update through it, it's the underlying tables that must be modified. How can you update a view column that is generated through a combination of multiple base columns?

The ACTOR_FULLNAME column in the view is a concatenation of two base columns and a literal. Oracle has no way of knowing what you mean by updating that column. Should it change ACTOR.ACTOR_NAME, ACTOR.ACTOR_SURNAME, or both? And how can it update either or both in a way that will behave correctly after the update, given that the expression for the virtual column always includes a space?

If you have some meaningful logic you want to implement to handle such an UPDATE, you could use an INSTEAD OF trigger on the view to implement it.

Answer 2

Your view contains a virtual column Actor_FullName, which is a concatenation of two other real columns.

You can only update views that meet certain criteria, or have an associated INSTEAD OF trigger. Read more about this at PSOUG.org.