Short Reminder – why you get ORA-00942: table or view does not exist inside a fuction while you can select from it in SQLPlus

This short and quick reminder explains why you can select from a a table or a view in SQLPlus but you get a ORA-00942: table or view does not exist when you try to compile a function using the same query.

This mostly happens when you try to use v$ views (v$session ,v$sesstat, v$statname etc.) inside a function.
in this case, even if the user is granted with dba role he can query the view but he can not use it inside plsql.

A user with the DBA role can access the v$ tables, because the select access is included in the DBA role. According to Oracle, privileges granted via roles are not inherited by stored objects. so, packages, procedures, views, and triggers need explicit grants, not grants via roles.

The solution is to grant access to object directly to the user. log in to the DB as sys and grant the privileges on a specific object (sys.v$instance ,for example ) to the user itself,
grant select on sys.v$instance to some_user;

Share on RedditEmail this to someoneShare on Google+Share on LinkedInShare on FacebookDigg thisTweet about this on Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *