1

I wanted to delete some records which i added recently, from this table mytemp, please tell me what is wrong with this query, data in selected column had been populated using cursor 

DELETE FROM (SELECT ROWNUM RM, S from mytemp) where rm > 20;

error is:

ORA-01732: data manipulation operation not legal on this view

ratsy
  • 575
  • 4
  • 7
  • 13

2 Answers2

2

Edited for accuracy...

Here's the description of the error you are getting:

http://ora-01732.ora-code.com/

An attempt was made to use an UPDATE, INSERT, or DELETE statement on a view that contains expressions or functions or was derived from more than one table. If a join operation was used to create the view or the view contains virtual columns derived from functions or expressions, then the view may only be queried.

So it looks like an updateable view can be substituted for a table, as long as it doesn't join more than one table or use virtual columns. In your case, the problem is the virtual ROWNUM column.

Dan A.
  • 2,914
  • 18
  • 23
  • thanks, but how can we use 'rownum >20' ,wouldnt it always return false..i want to confirm can we only select using an inline view..we cannot do data manipulation using inline views..? – ratsy Jan 27 '12 at 22:06
  • You're right, that was a bad solution. I edited my post to explain why it doesn't work and took down the faulty SQL. – Dan A. Jan 27 '12 at 22:13
  • Thanks Danimal, i still don understand..what is virtual rownum column? i mean that inline view gives output as 'rm' for rownum column & then am deleting those records where rm values are greater than 20..can u also explain about this virtual rownum..thanks again – ratsy Jan 27 '12 at 22:19
  • I just mean that you are using "rownum" in your inline view to generate the RM column. Presumably it is considered a column derived from a function, so your view is ineligible for update, insert, or delete based on Oracle's documentation. – Dan A. Jan 27 '12 at 22:34
  • Hmm..i think you are right..i thought ths error is due to reason that values in that column are populated using cursor..but yes might be its considered column derived from function or expression.. – ratsy Jan 27 '12 at 22:53
1

It's the rownum>20 statement.

ROWNUM>x, where x values greater than a positive integer are always false.

select * from ANYTABLE where rownum>(ANY POSITIVE INTEGER)

doesn't return any record.

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.

Check THIS for further info.

You can do the following:

delete from (select amount from TABLE t where t.amount=1000)

but it's the same as 

delete from TABLE where amount=1000
bonsvr
  • 2,262
  • 5
  • 22
  • 33
  • thnx bonsvr, ya know this but what i want to do is deleting soem records via inline view..i think we can select using inline view n by making rownum as an actual column... – ratsy Jan 27 '12 at 22:34
  • @ratsy yes you can. `alter table mytemp add row_col number; update mytemp set row_col=rownum;` – bonsvr Jan 27 '12 at 22:37
  • Thanks bonsvr, yes that solved my purpose for tiem being :) thank...but i still want to know whether we cannot delete using inline view – ratsy Jan 27 '12 at 22:57