Database Reference
In-Depth Information
The last two columns break up the PLAYING_TIME column into min-
utes and seconds. If you try to insert a new row using those last two col-
umns, an error will be returned (ORA-01733: virtual column not allowed
here). Nonetheless, you can still use the other columns to insert a row as in
the following script:
INSERT INTO SONG_VIEW (SONG_ID, ARTIST_ID, TITLE
, RECORDING_DATE)
VALUES (SONG_ID_SEQ.NEXTVAL,
(SELECT ARTIST_ID FROM ARTIST WHERE NAME='Jewel')
, 'Happy Birthday','15-JUL-02');
Notice that a list of columns was included in the INSERT command to
specify which of the view's columns to use in the insert.
Many simple views are capable of being used to modify data in the base
table. Sometimes, this is not the intent of the view. To ensure that the view
is never used for updating, you can create it with the WITH READ ONLY
clause as in the following example:
CREATE OR REPLACE VIEW OLDMUSIC_VIEW AS
SELECT MUSICCD_ID, TITLE CDNAME
, PRESSED_DATE, PLAYING_TIME
FROM MUSICCD
WHERE PRESSED_DATE < '01-JUL-01'
WITH READ ONLY;
Simple views and simple constraint views are really not too much differ-
ent from inserting into the base table itself, simply having more limitations.
19.5.2.1
DML and Views with Joins
Modifying data through a view that joins two tables is tricky. In addition to
all the rules that Oracle Database 10
g
imposes on simple views, there are
still more rules for views with joins. The most important ones to know are
as follows:
All the rules for simple views.
The primary key column(s) must be included for one of the tables.
