SQL Error ORA-00942 table or view does not exist

Question

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

User · Answer

I am using Oracle Database and i had same problem. Eventually i found ORACLE DB is converting all the metadata (table/sp/view/trigger) in upper case.

And i was trying how i wrote table name (myTempTable) in sql whereas it expect how it store table name in databsae (MYTEMPTABLE). Also same applicable on column name.

It is quite common problem with developer whoever used sql and now jumped into ORACLE DB.

User · Answer

Either the user doesn t have privileges needed to see the table  the table doesn t exist or you are running the query in the wrong schema Does the table exist      select owner              object name      from dba objects      where object name   any   CUSTOMER   customer     What privileges did you grant      grant select  insert on customer to user   Are you running the query against the owner from the first query

User · Answer

Case sensitive Tables  table names created with double-quotes  can throw this same error as well  See this answer for more information   Simply wrap the table in double quotes   INSERT INTO  customer   c id name surname  VALUES   1   Micheal   Jackson

User · Answer

Issue could be with different table might not exists or grant privilege is not for that table  mapped due to foreign key or synonym  For me the issue was with a column in that table which had mapping with another schema-table  and it was missing ex  public-synonym

User · Answer

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

User · Answer

Here is an answer  http   www dba-oracle com concepts synonyms htm  An Oracle synonym basically allows you to create a pointer to an object that exists somewhere else  You need Oracle synonyms because when you are logged into Oracle  it looks for all objects you are querying in your schema  account   If they are not there  it will give you an error telling you that they do not exist

User · Answer

You cannot directly access the table with the name  customer   Either it should be  user1 customer  or create a synonym  customer  for user2 pointing to  user1 customer   hope this helps

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

Examples related to sql

Examples related to oracle

Examples related to oracle11g