[sql] 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?

This question is related to sql oracle oracle9i ora-00942

The answer is

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>;

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;

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.

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

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to oracle

concat yesterdays date with a specific time ORA-28001: The password has expired how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Find the number of employees in each department - SQL Oracle Query to display all tablespaces in a database and datafiles When or Why to use a "SET DEFINE OFF" in Oracle Database How to insert date values into table error: ORA-65096: invalid common user or role name in oracle In Oracle SQL: How do you insert the current date + time into a table?

Examples related to oracle9i

ORA-01017 Invalid Username/Password when connecting to 11g database from 9i client How to select only 1 row from oracle sql? ORA-01008: not all variables bound. They are bound Oracle PL/SQL - How to create a simple array variable? ORA-12560: TNS:protocol adaptor error ORA-00942: table or view does not exist (works when a separate sql, but does not work inside a oracle function) UTL_FILE.FOPEN() procedure not accepting path for directory? Get a list of all functions and procedures in an Oracle database Best way to do multi-row insert in Oracle?

Examples related to ora-00942

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