CODEWITHSUNDEEP
oracle-databaseoracle-apex
JB999
JB999

Reputation: 507

Oracle Apex ORA-00942: table or view does not exist but it does exist

I worked on an App using my personal workspace. Exported the same and later installed it on a Developer Team workspace. Several of my pages are getting the ORA-00942: table or view does not exist while running, which doesn't make sense since the tables do exist (I tested this using the 'SQL Commands' option). Funny thing is that the same code I'm using is actually applied on a different app within the same workspace and it runs perfectly. Is this normal behavior? Might be due to workspace's configuration?

Thanks!

Upvotes: 2

Views: 2939

Answers (2)

paulsm4
paulsm4

Reputation: 121649

It could be a "permissions" issue:

http://www.dba-oracle.com/sf_ora_00942_table_or_view_does_not_exist.htm

Answer: There are several common operations that cause a ORA-00942 error:

  • Table owner name not specified when logged-in as a non-creator of the table.
  • ORA-00942 on table import (imp or impdp).
  • ORA-00942 on materialized view refresh.

...

This ORA-00942 error on insert is common when the user you are signed-on as does not have permission to see the table!

Either make the table public and grant DML privileges:

  • connect myuser/mypass
  • create public synonym testtable for myuser.testtable
  • grant insert, select, update, delete on mytable to public;

Also, review the various replies to this thread. For example:

https://stackoverflow.com/a/36165446/421195

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.

Upvotes: 2

Scott
Scott

Reputation: 5035

First thing I would check is the parsing schema for the application - see Shared Components -> Security Attributes.

If this is not the same between the two instances of your application, that's the likely explanation.

Upvotes: 2

Related Questions