Search code examples
sqloracleoracle9iora-00942

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

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?

Solution

  • 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