ORA-01732: операция манипуляции данными не допустима на этом обзоре
Причина:
Вы пытаетесь использовать UPDATE, INSERT, или DELETE оператор на обзоре который содержит выражения или функции или был произведен более чем из одной таблицы. Если операция соединения была использована для создания обзора или обзора содержащего виртуальные колонки произведенные от функций или выражений, значит этот обзор может только запрашиваться.
Действие:
Измените, вставьте, или удалите строки в базовых таблицах взамен, защитите ваши операции на обзоре.
May 3, 2021
I got ” ORA-01732: data manipulation operation not legal on this view ” error in Oracle database.
ORA-01732: data manipulation operation not legal on this view
Details of error are as follows.
ORA-01732: data manipulation operation not legal on this view Cause: 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. Action: UPDATE, INSERT, or DELETE rows in the base tables instead and restrict the operations on the view to queries.
data manipulation operation not legal on this view
This ORA-01732 errors are related with the 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.
UPDATE, INSERT, or DELETE rows in the base tables instead and restrict the operations on the view to queries.
Don’t try to update a view except with INSTEAD OF triggers
Or
To solve this error, Set the _simple_view_merging to TRUE and execute SQL statement again.
ALTER SESSION SET "_simple_view_merging"=TRUE scope=both
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )
Hi Gurus I am getting the error ORA-01732: data manipulation operation not legal on this view
when executing the below query
UPDATE (SELECT CR.AMOUNT AS AMOUNT,
CASE
WHEN MRG.AMOUNT_USD=0
THEN CR.AMOUNT
ELSE MRG.AMOUNT_USD
END AS AMOUNT_BILAT,
CR.ISUPDATED
FROM CRS_TT_BILAT_EXCL_MERGE1 MRG,CRS_T_CURRENT_RATES1 CR
WHERE SUBSTR(CR.DNIS_CD,1,3)=MRG.DNIS_CD
AND CR.PRODUCT_CUST_ID = MRG.PRODUCT_CUST_ID
AND CR.ISUPDATED <> 'Y'
AND ROWNUM = 1)
SET AMOUNT = AMOUNT_BILAT;
CR.ISUPDATED = 'Y';
I have simplified the above code from the below query
UPDATE CRS_T_CURRENT_RATES1 CR
SET CR.AMOUNT =
(SELECT
CASE
WHEN MRG.AMOUNT_USD=0
THEN CR.AMOUNT
ELSE MRG.AMOUNT_USD
END
FROM CRS_TT_BILAT_EXCL_MERGE1 MRG
WHERE SUBSTR(CR.DNIS_CD,1,3)=MRG.DNIS_CD
AND CR.PRODUCT_CUST_ID = MRG.PRODUCT_CUST_ID
AND ROWNUM = 1),
CR.ISUPDATED = 'Y'
WHERE EXISTS
(SELECT 1 FROM CRS_TT_BILAT_EXCL_MERGE1 MRG WHERE MRG.DNIS_CD = SUBSTR(CR.DNIS_CD, 1,3) AND CR.PRODUCT_CUST_ID = MRG.PRODUCT_CUST_ID )
AND
CR.ISUPDATED <> 'Y';
I was trying to optimize the 2nd query, since the second query uses two selects i was trying to replace that with a single query. Can any one please help me on this?
Skip to content
I want to remove duplicate id data with following sql, but I am getting the following error then
gettin error ORA-01732: data manipulation operation not legal on this view
delete
from MV_JTZC_GBJG
where id in
(select id from MV_JTZC_GBJG group by id having count(id) > 1)
and rowid not in (select min(rowid)
from MV_JTZC_GBJG
group by id
having count(id) > 1);
>Solution :
Looks like you’re deleting from a (materialized, MV_…) view. If so, Oracle says
ORA-01732: data manipulation operation not legal on this view
Cause: 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.
Action: UPDATE, INSERT, or DELETE rows in the base tables instead and restrict the operations on the view to queries.
Which means that you should delete rows from underlying table(s), and not from the (materialized) view. Perhaps you could, if materialized view was created with the for update clause (used for replication).
On the other hand, maybe query – that was used to create that (materialized) view – isn’t good and it causes duplicates to appear. In that case, fix the query (e.g. maybe some join conditions are missing).
I’ve tried to research about the subject, but it seems to be this error is more related to views and materialized views than to tables… Having attempted to solve it by my own in vain, here’s my simplified setup.
I’ve got a table DDL similar to the following:
CREATE TABLE "TEST"."COUNTRY"
( "ID_COUNTRY" NUMBER INVISIBLE NOT NULL ENABLE,
"COD_COUNTRY" CHAR(3 BYTE) DEFAULT NULL,
"DESC_COUNTRY" CHAR(3 BYTE) DEFAULT NULL,
"ID_XYZ" CHAR(5 BYTE),
PRIMARY KEY ("ID_COUNTRY")
TABLESPACE "MY_TBS" ENABLE;
GRANT DELETE ON "TEST"."COUNTRY" TO "USER01";
GRANT INSERT ON "TEST"."COUNTRY" TO "USER01";
GRANT SELECT ON "TEST"."COUNTRY" TO "USER01";
GRANT UPDATE ON "TEST"."COUNTRY" TO "USER01";
Trying to re-order the columns of the table with the invisible/visible method, I was able to do it easily in some tables as expected. Yet there are others I have trouble doing so that doesn’t seem to have anything special or different to the others this method worked… Here’s an example using the table I shared above:
ALTER TABLE COUNTRY MODIFY (COD_COUNTRY INVISIBLE, DESC_COUNTRY INVISIBLE)
Table COUNTRY altered.
ALTER TABLE COUNTRY MODIFY (COD_COUNTRY VISIBLE, DESC_COUNTRY VISIBLE)
Error report -
ORA-01732: data manipulation operation not legal on this view
01732. 00000 - "data manipulation operation not legal on this view"
*Cause:
*Action:
I’d like to be able to make those columns visible again and understand why I got this error about views even if I’m attempting these statements in a table.