OraFAQ Forum: SQL & PL/SQL » ORA-01732: data manipulation operation not legal on this view

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01732: data manipulation operation not legal on this view

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01732: data manipulation operation not legal on this view [message #640261]

Sun, 26 July 2015 00:54

Ashu_26
Messages: 3
Registered: July 2015

Junior Member

Hi All,

I am trying to delete the contents of a table but getting the error ORA-01732: data manipulation operation not legal on this view. I checked in the Materialized view section and found one of the MV has the same name as my table but this MV is created using some other tables. How can i differentiate between my table and MV.

delete from <tablename> - How should i modify this statement so that it will contain the table not the MV.

Regards,
Ashu

Report message to a moderator

Re: ORA-01732: data manipulation operation not legal on this view [message #640263 is a reply to message #640261]

Sun, 26 July 2015 01:17

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

Creating a materialized view implicitly creates a materialied view container table of the same name. You cannot do DML against this, unless it was created with the FOR UPDATE clause that is intended for use with replication.

[Updated on: Sun, 26 July 2015 01:18]

Report message to a moderator

Re: ORA-01732: data manipulation operation not legal on this view [message #640265 is a reply to message #640263]

Sun, 26 July 2015 01:30

Ashu_26
Messages: 3
Registered: July 2015

Junior Member

Hi John,

Thanks for the response. But my doubt is the MV is made of some different tables not from the table i am doing the delete operation. how can i proceed on this. Table name is HRMS_Meta, MV has the same name but it is made of some different tables. Please see below:

CREATE MATERIALIZED VIEW "SMARTHYP"."HRMS_META" ("ORACLE_ID", "FIRST_NAME", "LAST_NAME", "PARENT", "ALIAS1", "ALIAS2")
<Properties>
....
....
<Properties>
AS SELECT DISTINCT
<Col. Definition>
<Col. Definition>
.....
.....
<Col. Definition>
from PER_ALL_PEOPLE_F@TSMRTIHYP PAPF, PER_PERSON_TYPE_USAGES_F@TSMRTIHYP PPTU

Do you think its a bad db design and some how i need to change the delete statement so that it can recognize that hrms_meta is table not the MV.
Please advise.

Regards,
Ashu

Report message to a moderator

Re: ORA-01732: data manipulation operation not legal on this view [message #640266 is a reply to message #640265]

Sun, 26 July 2015 01:35

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

To repeat: You cannot do DML against a materialized iew, unless it was created for the purpose of replication with the FOR UPDATE clause.

Next time you post, please enclose any code in [code] tags to make it readable.

Report message to a moderator

Re: ORA-01732: data manipulation operation not legal on this view [message #640267 is a reply to message #640266]

Sun, 26 July 2015 01:41

Ashu_26
Messages: 3
Registered: July 2015

Junior Member

Hi John,

>> You cannot do DML against a materialized iew,<<
Pardon my ignorance. I am new in SQL. I want to do the DML for Table not for materialized view.

Regards,
Ashu

Report message to a moderator

Re: ORA-01732: data manipulation operation not legal on this view [message #640268 is a reply to message #640267]

Sun, 26 July 2015 01:44

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

I give up. Perhaps someone else will try.

Report message to a moderator

Re: ORA-01732: data manipulation operation not legal on this view [message #640270 is a reply to message #640267]

Sun, 26 July 2015 02:48

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Ashu_26 wrote on Sun, 26 July 2015 08:41

Hi John,

>> You cannot do DML against a materialized iew,<<
Pardon my ignorance. I am new in SQL. I want to do the DML for Table not for materialized view.

Regards,
Ashu

What goal are you trying to achieve?
In other words, why do you want to do this DELETE?

Report message to a moderator

Previous Topic:	Rewriting a sql with NOT to avoid the NOT and OR clause
Next Topic:	History Tables Search Criteria

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 14:15:03 CDT 2024