Ошибка 942 oracle

Because this post is the top one found on stackoverflow when searching for «ORA-00942: table or view does not exist insert», I want to mention another possible cause of this error (at least in Oracle 12c): a table uses a sequence to set a default value and the user executing the insert query does not have select privilege on the sequence. This was my problem and it took me an unnecessarily long time to figure it out.

To reproduce the problem, execute the following SQL as user1:

create sequence seq_customer_id;

create table customer (
c_id number(10) default seq_customer_id.nextval primary key,
name varchar(100) not null,
surname varchar(100) not null
);

grant select, insert, update, delete on customer to user2;

Then, execute this insert statement as user2:

insert into user1.customer (name,surname) values ('michael','jackson');

The result will be «ORA-00942: table or view does not exist» even though user2 does have insert and select privileges on user1.customer table and is correctly prefixing the table with the schema owner name. To avoid the problem, you must grant select privilege on the sequence:

grant select on seq_customer_id to user2;

The ORA-00942: table or view does not exist error indicates that you are trying to run a SQL query that refers to a non-existent table or view, or that the appropriate permissions and privileges do not exist, or that the table and view names are misspelt. The referencing table or view does not exist, or you are attempting to use a table without the appropriate permissions or privileges. Check that the table and view names are correctly spelt, and that the view is not being used in areas where a table is required.

The table or view is either not created or it is removed from the database. When you try to execute the table, oracle shows error message as table or view does not exist. It’s possible that the database user lacks the necessary permissions to execute the table or view. If Oracle cannot find the table in the database, the error “ORA-00942: table or view does not exist” will be thrown.

The table or view did not exist because it was never created, which is a typical cause of the “ORA-00942: table or view does not exist.” error. If you’re not sure whether a view, table, or synonym exists, use the data dictionary to get a list of all tables and views. You’re referring to a table or view in another schema that wasn’t created by you. You must refer to the table by its schema name when running the query from another schema.

ORA-00942: table or view does not exist error occurs if the table or view does not exist in the database or that the appropriate permissions and privileges do not exist, or that the table and view names are misspelt.

When the ORA-00942 error occurs

If the table or view is not created or removed from the database, the Oracle error “ORA-00942: table or view does not exist” occurs. The error will be thrown if the table or view does not have the necessary permissions and privileges. The employee table is not created in the database in the following example. The select sql query is executed on the database’s employee table. The employee table could not be found in the Oracle database. As a result, Oracle throws an error stating that the table or view does not exist.

select * from emp;

Error starting at line : 35 in command -
select * from emp;
Error report -
ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"

Root Cause

The table or view that was requested does not exist in the database. The database table or view was either not created or was removed. The sql query searches for a table or view in the database objects list. The SQL error will be thrown because the table or view could not be found in the database. It’s possible that the user that executes the sql query lacks the necessary table or view permissions. If the table does not exist in the database, the user does not have the necessary privileges and permissions to execute, or the table name is misspelt, the ORA-00942: table or view does not exist error will be thrown.

Solution 1

The table or view name in the sql query may be misspelt. Check the table and view names in the sql query. If the name is misspelt, update it to the correct name in the sql query. The misspelt table or view will not be present in the database sql. The ORA-00942: table or view does not exist error will be thrown. Oracle searches the database for the specified table name. The table with the misspelt name does not exist in the database. The error will be thrown by the Oracle database.

Solution 2

The error is typically caused by a table or view not being created or deleted from the database. If the table or view does not already exist in the database, create it. The sql query will run without the error “ORA-00942: table or view does not exist” when the table or view is created in the database. This query cannot be executed if the table has been deleted. Remove the sql query that will be executed in the database. If the table is accidentally removed, recreate it and then run the query to resolve the error.

create table employee
(
empid integer primary key,
me-name varchar2(100)
);

select * from employee;

Solution 3

The table or view can be created using multiple schemas. It’s possible you’re switching schemas. The sql query was unable to find the table or view from the existing schema. In this case, the schema name should appear before the table name. The current schema must have the necessary permissions in order to call a table or view from another schema. The schema name should be prefixed by the name of the table or view. Before running the table from another schema, the necessary permissions should be granted.

select * from <schema_name>.<tablename>;

select * from hr.employee;

Solution 4

When the view is created, the underlying table may be unavailable or removed, or it may not have the necessary permissions to execute. In this case, the error will be thrown. Check that the underlying database objects, such as tables, types, and sequences, are accessible and have the necessary permissions.

create table employee 
(
empid integer primary key,
empname varchar2(100)
);

create view emplist as select * from employee;

drop table employee;

select * from emplist;

Solution 5

If the table is created using a sequence that does not have sufficient permission to execute, the error will be thrown. The table is created using the sequence. If you insert a row into the table, the table will be unable to run the sequence and determine the next value to enter into the auto increment column.

create sequence seq_emp;
create table employee 
(
empid integer default seq_emp.nextval primary key,
empname varchar2(100)
);

insert into employee('Yawin');

The below permissions should be added to execute the sequence if an another user executes the insert statement.

grant select on seq_emp to emp2;