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

sql oracle oracle9i ora-00942
Victor picture Victor · Jul 12, 2011 · Viewed 127.1k times · Source

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?

Answer

Steve Broberg picture Steve Broberg · Jul 12, 2011

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;

Related questions

ORA-12560: TNS:protocol adaptor error

I Google[d] for this error ORA-12560: TNS:protocol adaptor error but not able to find the actual reason and how to solve this error ? Can anyone tell me a perfect solution to solve login problem.

Read More
How to select only 1 row from oracle sql?

I want to use oracle syntax to select only 1 row from table DUAL. For example, I want to execute this query: SELECT user FROM DUAL ...and it'd have, like, 40 records. But I need only one record. ...AND, I want to …

Read More
Best way to do multi-row insert in Oracle?

I'm looking for a good way to perform multi-row inserts into an Oracle 9 database. The following works in MySQL but doesn't seem to be supported in Oracle. INSERT INTO TMP_DIM_EXCH_RT (EXCH_WH_KEY, EXCH_NAT_KEY, EXCH_…

Read More