When using a query in CA-Aion, why do I receive a ?Oracle error 942: table or view does not exist"?

Document ID : KB000023067
Last Modified Date : 14/02/2018
Show Technical Document Details

Question:

If I receive a "Oracle error 942: table or view does not exist", how can I resolve this? Does it have anything to do with the CA Aion code or CA Aion IDE itself?

Answer:

The cause or reason for Oracle error 942 message is because Oracle tries to execute an SQL statement that references a table or view that either does not exist, or because a synonym that is not allowed here was used, or because you do not have access rights to the particular object. Other possible cause is that the table or view belongs to another schema and you didn't reference the table by the schema name, or a view was referenced where a table is required.

Depending on the cause of the problem, there are several resolutions or remedies to resolve this error. Due to there being several possible reasons that may cause the error, there are several actions or steps that you can take to identify where the error is and take the appropriate workaround or solution.

    • Check if user tables and views exist in Oracle by querying the data dictionary by executing the following SQL statement:
       select * from all_objects where object_type in (`TABLE','VIEW') and object_name = `OBJECT_NAME`;
      Replace OBJECT_NAME with the name of the table or view that you want to verify its existence.



    • If the table or view exists, check and verify if the user has the necessary permissions and rights to read and access (i.e. select) from the table, as certain privileges may be required to access the table. In this case, you will need to have the owner of the table or view, or a Oracle DBA grant the appropriate privileges, permissions or rights to this object. Note that when selecting from a system view (such as v$session) the privilege or access rights to select from the view must be granted to the user directly and not via a role. This is the case if you can select from the view without problem but then get this error when trying to create a view on it.



    • If the table or view exists but is in a different schema from the current schema where the SQL is executing (in other word, the table isn't owned by you, but owned by another user), the Oracle error 942 will be returned. Resolve this by explicitly referencing the table or view by specifying the schema name, i.e. schema_name.table_name.

This approach should help diagnose an Oracle error 942 with Oracle tables in CA Aion, irrespective of the code itself. Many Oracle error 942 errors are caused by problems not related to the CA Aion code or the CA Aion IDE itself.