Insert question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Insert question

  1. #1
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    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

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Views based on ROWNUM do not allow any DML against it.

    Sanjay

  3. #3
    Join Date
    May 2002
    Posts
    2,645
    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.

  4. #4
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    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
  •  



Click Here to Expand Forum to Full Width