ORA-01732: data manipulation operation not legal on this view
Question: What causes the ORA-01732 error?
ORA-01732: data manipulation operation not legal on this view.
Answer: The oerr utility shows this for the ORA-01732 error, showing that you cannot issue any update DML against a view:
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.
Instead, you need to find the base tables from the view and issue the updates against the base tables. You cannot update a view except with INSTEAD OF triggers if the defining query of the view contains one of the following constructs:
A set operator
A DISTINCT operator
An aggregate or analytic function
A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
A collection expression in a SELECT list
A subquery in a SELECT list
A subquery designated WITH READ ONLY
Joins, with some exceptions . . .
As we see, the ORA-01732 can be fixed by issuing the DML against the base tables.
Oracle Training from Don Burleson
The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
Question: What causes the ORA-01732 error?
ORA-01732: data manipulation operation not legal on this view.
Answer: The oerr utility shows this for the ORA-01732 error, showing that you cannot issue any update DML against a view:
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.
Instead, you need to find the base tables from the view and issue the updates against the base tables. You cannot update a view except with INSTEAD OF triggers if the defining query of the view contains one of the following constructs:
A set operator
A DISTINCT operator
An aggregate or analytic function
A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
A collection expression in a SELECT list
A subquery in a SELECT list
A subquery designated WITH READ ONLY
Joins, with some exceptions . . .
As we see, the ORA-01732 can be fixed by issuing the DML against the base tables.
Oracle Training from Don Burleson
The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
No comments:
Post a Comment