Insert question

[denevge]

Take a look at this :

SQL> create table itest ( id number, name varchar2(20));
Table created.

SQL> insert into ( select * from itest ) values (1,'My');
1 row created.

SQL> insert into ( select * from itest where 1=2 ) values( 2,'Myself');
1 row created.

SQL> insert into ( select * from itest where rownum < 1 ) values ( 3,'Me Again');
insert into ( select * from itest where rownum < 1 ) values ( 3,'Me Again')
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

SQL> insert into ( select * from itest where id = 9999) values ( 4,'MySelf Again');
1 row created.

Why is the second insert OK and the third not ?

Thanks
Gert

[SANJAY_G]

Views based on ROWNUM do not allow any DML against it.

Sanjay

[stecal]

And besides, your use of rownum is wrong - unless you were trying to not get any rows returned.

You can use rownum = 1 (the only time the "=" sign is allowed),
or rownum < some_other_number_greater_than_1.

[denevge]

well that was what I found so strange.

if i use
where 1=2, no rows will be returned because the condition is never true, and still, the insert gets done.
where rownum < 1, I also will never by true, and it's not working.

If I do the select :

SQL> select * from itest where 1=2
no rows selected

SQL> select * from itest where rownum < 1;
no rows selected

The result for the 2 statements is the same, so I was wondering why 1=2 works and why rownum<1 not .

But,
as SANJAY_G said, it is caused by the rownum in the view definition.

Thanks for the response
Gert