Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Error: ORA-00942 table or view does not exist

I use SQL developer and i made a connection to my database with the system user, after I created a user and made a another connection with that user with all needed privileges.

But when I try to proceed following I get the SQL Error

ORA-00942 table or view does not exist.:


INSERT INTO customer (c_id,name,surname) VALUES ('1','Micheal','Jackson') 
like image 881
Yurtsevero Avatar asked Apr 21 '13 09:04

Yurtsevero


People also ask

How resolve table or view does not exist in SQL Developer?

check the spelling of the table or view name. check that an existing table or view name exists. contact the DBA if the table needs to be created or if user or application privileges are required to access the table.

What is the meaning of table or view does not exist in SQL?

It means exactly what it says, the table or view you are executing your query on does not exist in your schema. To explain, whenever you execute a query which includes a table, view, synonym or a cluster which does not exist into the schema with which you are connected the SQL engine will show you this error.

What could be the reason for failure with error table or view does not exist in Informatica?

This error will occur when the table name prefix is not specified for the table. The Table name prefix is a requirement for loading in bulk mode for Oracle 9i. 1) For Solution, enter CR with a Workaround if a direct Solution is not available.


1 Answers

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; 
like image 167
jake stayman Avatar answered Oct 13 '22 10:10

jake stayman