CODEWITHSUNDEEP
oracle-databasecompiler-warnings
Prabhat
Prabhat

Reputation: 45

Compiling the force view is giving error

We are facing an issue with force view in oracle. Certain underlying objects of the view were modified to add new columns. Post that when we are compiling the view with alter view < view name> compile, we are getting the error

warning: view altered with compilation errors.

On checking the error, we got :

ORA-00942: table or view does not exist.

But when we are executing the view select query it is executing fine and extracting data.

Can somebody help me to fix this issue with view compilation?

Upvotes: 0

Views: 894

Answers (1)

APC
APC

Reputation: 146309

"we are executing the view select query it is executing fine "

So it seems the underlying query selects data from a table in another schema i.e. not the schema trying to create a view, or in some other way references an object in another schema.

What has happened is the table owner granted privileges on its table to a role, which is granted to the querying schema. Privileges granted through a roles allow us to write SQL against the granted objects but not to build programmatic objects (PL/SQL, views, etc). To do that, the table owner needs to grant the privileges directly to the querying user.

This is a limitation imposed by the Oracle security model. There is no other workaround.

Upvotes: 2

Related Questions