| How to use a Cursor and a View to make updates [message #4370] |
Tue, 03 December 2002 08:04  |
Mark Grimshaw
Messages: 73
Registered: June 2002
|
Member |
|
|
I want to ise a view to define a cursor and then to update the record later but get the Oracle error -
"ORA-02014 cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.".
My code from a PL/SQL script is below:-
DECLARE
MyName MyTable.NAME%TYPE;
MyRec MyTable%ROWTYPE;
CURSOR mycursor IS SELECT * FROM MyView WHERE NAME = MyName FOR UPDATE;
--Set search criteria
MyName := 'Jim';
OPEN mycursor;
FETCH c1 INTO MyRec;
UPDATE MyView SET SALARY = 1000 WHERE CURRENT OF mycursor;
I can get the above logic to work if I set up the cursor to read from the underlying base table used by MyView but I want to completely shield clients from the tables. Does anyone know how I can do this without resorting to other mechanisms like Stored Procedures?
TYIA
Mark Grimshaw
|
|
|
|
| Re: How to use a Cursor and a View to make updates [message #4397 is a reply to message #4370] |
Wed, 04 December 2002 22:54  |
seng
Messages: 191
Registered: February 2002
|
Senior Member |
|
|
PL/SQL is working fine. But your can't update view which has certain condition to allow update, like GROUP BY. You can do it with view without any condition.
Update/Insert/Delete record in view needs understanding the structure of baseline table especially like FK, PR and someone.
Hope this is helping. Thanks
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
