-
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
-
Views based on ROWNUM do not allow any DML against it.
Sanjay
-
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.
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|