how to update a view in Oracle on sqlplus?  ASAP...

cap45 used Ask the Experts™
I'm having trouble with updating and deleting rows from a view I created "ViewAppt".

I have two cursors that i use to compare the data in the rows of the view. I have conditional statements to check where I would like to update a column in the row or delete the row.

when i compile i get this error

PLS-00904: insufficient privilege to access object VIEWAPPT

I thought view could be update any suggestion asap...
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Are the view and the 'compiled' PL/SQL (procedure/function/trigger) under different users ?
If so, is the grant based on a role ?

For PL/SQL to work, the grant should be directly to the owner of the PL/SQL object.

From what i know, we cannot update or delete a View.

Good Luck ^0^

views from a two different tables cannot be updated or deleted very easily becoz it depends upon different other criteron like the constraints present in the tables which ur linking to form a view. So check out for the constraints. look inot user_constraints table. if u could post ur code along with oracle system error this could give more insight into the error.

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!


updation and deletion can be done if u have view from one single table. or if u don't have any sort of constraints on the table by which u r going to form a view.

The only problem here is the GRANTS - as the error message PLS-00904 says!

from the scheme/user your view is created in just make an "grant all on viewappt to _your_user_;" - that will do!

Cheers, Stefan

stemu is right. I just like to point out that it is NOT sufficient to grant to one of the users role only. The creator of a procedure must have the necesary rights on the objects used directly.

The reasons for this behaviour is to prevent bypassing of the grant system via creation of procedures by "simple users" and the indifference of the package states from changeds in the role system.
     This Problem can be because of Three Reasons,

     1. You may not have Permission to access and update or delete the view.
     2. When a view is made up of two or more tables, u can update or delete any one or no tables used in the view. The table u can delete should be a Key Preserve Table. What it meant is the tables Primary Key should be the Primary key in the view to be updatable or deletable.
     3. Or the View can be created as Not Updatable with only readable.

Revert Back for Details


Once all the permissions are checked and found ok, use "Instead Of" triggers to update the underlying tables for this view.

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept stemu2000's comment as answer
Please leave any comments here within the next seven days.
EE Cleanup Volunteer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial