oracle: why cant i insert into view

I created a simple view with columns from a single table. When i try to insert values into the table, i get the ORA_01732 error that dml is not legal on this view. I DO have an order by clause in the view definition which I've gathered is not allowed for it to be 开发者_如何学Goinherently updatable and I'm seeing that I probably have to use an INSTEAD OF type clause in the view definition. Can someone show me how I would build the view to be updatable this way?

here is the create view statement:

create view CUST_VIEW
as select customer#,firstname,lastname,state
from book_customer
order by state, lastname;

Are you sure that the order by is the cause for your observation?

I can do an insert on a view with an order by clause:

create table tq84_table (
  a number,
  b number
);

create view tq84_updateable_view as
select a, b from tq84_table 
order by a;


insert into tq84_table values (4,1);
insert into tq84_table values (1,4);
insert into tq84_table values (3,9);
insert into tq84_table values (7,5);

select * from tq84_updateable_view;

insert into tq84_updateable_view values (1,9);

select * from tq84_updateable_view;

The above statements run with no problem on Oracle 11 R2.

You might want to check with USER_UPDATABLE_COLUMNS which columns you can insert to:

SQL> select * from user_updatable_columns where table_name = 'TQ84_UPDATEABLE_VIEW';

OWNER                          TABLE_NAME                     COLUMN_NAME                    UPD INS DEL
------------------------------ ------------------------------ ------------------------------ --- --- ---
RENE                           TQ84_UPDATEABLE_VIEW           A                              YES YES YES
RENE                           TQ84_UPDATEABLE_VIEW           B                              YES YES YES

You are right that a view with ORDER BY clause is not inherently updatable. All you have to do is create an INSTEAD OF trigger on the view to execute the INSERT you want. Eg: Lets say you have a view ACTIVE_CUST_VIEW on table ALL_CUST

CREATE OR REPLACE TRIGGER INS_NEW_CUST_VIEW
INSTEAD OF INSERT
ON ACTIVE_CUST_VIEW
FOR EACH ROW
BEGIN
INSERT INTO ALL_CUST (CUST_ID,CUST_NAME,START_DATE) VALUES (:NEW.CUST_ID,:NEW.CUST_NAME,:NEW.START_DATE);
END INS_NEW_CUST_VIEW;
/