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

by

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;

More Related Contents:

  1. Best way to do multi-row insert in Oracle?
  2. ORA-12560: TNS:protocol adaptor error
  3. How do I limit the number of rows returned by an Oracle query after ordering?
  4. How to create id with AUTO_INCREMENT on Oracle?
  5. When do I need to use a semicolon vs a slash in Oracle SQL?
  6. LISTAGG in Oracle to return distinct values
  7. Fetch the row which has the Max value for a column
  8. Oracle SQL: Update a table with data from another table
  9. Is there an Oracle SQL query that aggregates multiple rows into one row? [duplicate]
  10. How to handle a single quote in Oracle SQL
  11. Select count(*) from multiple tables
  12. Oracle SQL – How to Retrieve highest 5 values of a column [duplicate]
  13. REGEX to select nth value from a list, allowing for nulls
  14. Concatenate and group multiple rows in Oracle [duplicate]
  15. Difference between BYTE and CHAR in column datatypes
  16. SQL to generate a list of numbers from 1 to 100
  17. How to use BOOLEAN type in SELECT statement
  18. Call a stored procedure with another in Oracle
  19. How to show all privileges from a user in oracle?
  20. create table with sequence.nextval in oracle [duplicate]
  21. Group by alias (Oracle)
  22. Split comma separated values of a column in row, through Oracle SQL query
  23. oracle — Split multiple comma separated values in oracle table to multiple rows
  24. Why can’t I seem to force Oracle 11g to consume more CPUs for a single SQL query
  25. Oracle (Old?) Joins – A tool/script for conversion?
  26. Oracle : select maximum value from different columns of the same row
  27. Split varchar into separate columns in Oracle
  28. Creating or simulating two dimensional arrays in PL/SQL
  29. How to retrieve the current value of an oracle sequence without increment it?
  30. Oracle Convert TIMESTAMP with Timezone to DATE

Leave a Comment