ORA-00942 table or view does not exist works when a separate sql but does not work inside a oracle function

Question

When I have a sql statement like select   from table1  it works great  but as soon as I put it into a function  I get   ORA-00942  table or view does not exist    How to solve this

User · Answer

Either u dont have permission to that schema/table OR table does exist. Mostly this issue occurred if you are using other schema tables in your stored procedures. Eg. If you are running Stored Procedure from user/schema ABC and in the same PL/SQL there are tables which is from user/schema XYZ. In this case ABC should have GRANT i.e. privileges of XYZ tables

Grant All On To ABC;

Select * From Dba_Tab_Privs Where Owner = 'XYZ'and Table_Name = <Table_Name>;

User · Answer

There s a strong chance that the privileges to select from table1 have been granted to a role  and the role has been granted to you   Privileges granted to a role are not available to PL SQL written by a user  even if the user has been granted the role   You see this a lot for users that have been granted the dba role on objects owned by sys   A user with dba role will be able to  say  SELECT   from V SESSION  but will not be able to write a function that includes SELECT   FROM V SESSION   The fix is to grant explicit permissions on the object in question to the user directly  for example  in the case above  the SYS user has to GRANT SELECT ON V  SESSION TO MyUser

User · Answer

A very simple solution is to add the database name with your table name like if your DB name is DBMS  and table is info  then  it will be DBMS info for any query   If your query is  select   from STUDENTREC where ROLL NO 1    it might show an error but  select   from DBMS STUDENTREC where ROLL NO 1     it doesn t because now actually your table is found

User · Answer

Make sure the function is in the same DB schema as the table

User · Answer

There are a couple of things you could look at  Based on your question  it looks like the function owner is different from the table owner   1  Grants via a role   In order to create stored procedures and functions on another user s objects  you need direct access to the objects  instead of access through a role    2       By default  stored procedures and SQL methods execute with the   privileges of their owner  not their current user    If you created a table in Schema A and the function in Schema B  you should take a look at Oracle s Invoker Definer Rights concepts to understand what might be causing the issue   http   download oracle com docs cd B19306 01 appdev 102 b14261 subprograms htm LNPLS00809

[sql] ORA-00942: table or view does not exist (works when a separate sql, but does not work inside a oracle function)

Examples related to sql

Examples related to oracle

Examples related to oracle9i

Examples related to ora-00942