cap45
asked on
how to update a view in Oracle on sqlplus? ASAP...
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...
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...
From what i know, we cannot update or delete a View.
Good Luck ^0^
Good Luck ^0^
Greetings,
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.
Regards
Shyam
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.
Regards
Shyam
Greetings,
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.
Shyam.
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.
Shyam.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
Dear
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
Regards
Dhivakar
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
Regards
Dhivakar
CAP45
Once all the permissions are checked and found ok, use "Instead Of" triggers to update the underlying tables for this view.
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.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
jpkemp
EE Cleanup Volunteer
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.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
jpkemp
EE Cleanup Volunteer
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.