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

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


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;


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


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