Thursday, December 06, 2007

ORA-00942: table or view does not exist

I was checking out a new apex view in the beta release of Apex 3.1, apex_application_groups, when trying to execute a trivial query like:
select * from apex_application_groups;
i got:
ORA-00942: table or view does not exist
After a short exchange of messages with the Apex team, it turned out that they had forgotten to create a public synonym for this view.

So, this could be a good occasion to write down a reminder of all the cases in which such error message can be returned:
  • referencing a non-existent table or view, including the following possibilities:
    • misspelled name
    • mismatching letter casing if the object was created with a case sensitive name
    • forgetting the double quotes around the object name when the object was created with a case sensitive name
  • referencing a table or view in a different schema, without having specified the schema name.
  • referencing a table or view in a different schema, misspelling the schema name (or referencing a non-existent schema anyway...)
  • referencing a non-existent, misspelled or case sensitive synonym
  • referencing a table, view or synonym without having the necessary privileges (for instance try SELECT * FROM DBA_USERS as a normal user).
You can even get this message in a very naive situation like that of referencing the correct name of a table, view or synonym and with all the necessary privileges, but the real object created by someone else with the wrong name... :-D

For some unknown reason ORA-00942 has disappeared from the Error Message Guide as of Oracle 10G, but you can still find the description of this error in version 9.2.

See message translations for ORA-00942 and search additional resources

No comments:

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles