ORA-00942 error is generating while creating a view

Question

I have created a new view named CONS_INTERRUPTED_DATA for the main user hfdora and the view has been created successfully. But when I am trying to create the same view for another user (cis) of the same database after giving all the privileges to the user (cis) I am getting the below error,

*oms_consumer

ERROR at line 13: ORA-00942: table or view does not exist

Both the user hfdora and cis are the part of same database and this oms_consumer table is present at the database

I have granted the following privileges for the user cis before creating the view

grant select on energization_info to cis;
grant select on trigger_info to cis;
grant select on oms_source to cis;
grant select on oms_consumer to cis;
grant connect,resource,dba to cis;

My sql query to create the view,

>CREATE OR REPLACE VIEW CONS_INTERRUPTED_DATA AS
    SELECT
        trigger_info_A.b1 AS FDR_RMU_OFF_B1, trigger_info_A.b2 AS FDR_RMU_OFF_B2,
        trigger_info_A.B3TEXT AS FDR_RMU_OFF_B3TEXT, trigger_info_A.elem AS FDR_RMU_OFF_ELEM,
        trigger_info_B.b1 AS FDR_RMU_RESTORE_B1, trigger_info_B.b2 AS FDR_RMU_RESTORE_B2,
        trigger_info_B.B3TEXT AS FDR_RMU_RESTORE_B3TEXT,
        trigger_info_B.elem AS FDR_RMU_RESTORE_ELEM,
        oms_consumer.consumer_code, energization_info.b1 AS AFFECTED_B1,
        energization_info.b2 AS AFFECTED_B2, energization_info.b3text AS AFFECTED_B3TEXT,
        to_char(energization_info.deenergized_date, 'DD-MM-YYYY Hh24:MI:SS') AS DEENERGIZED_DATE,
        to_char(energization_info.energized_date, 'DD-MM-YYYY Hh24:MI:SS') AS ENERGIZED_DATE,
        trigger_info_A.comments AS KEY
    FROM
        energization_info, 
        trigger_info trigger_info_A,
        trigger_info trigger_info_B, 
        oms_consumer
    WHERE
        (energization_info.trigger_number = trigger_info_A.trigger_number)
        AND (energization_info.ENERGIZED_TRIGGER_NUMBER = trigger_info_B.trigger_number)
        AND (energization_info.b1 = oms_consumer.B1NAME 
             AND energization_info.b2 = oms_consumer.B2NAME 
             AND energization_info.b3 = oms_consumer.B3NAME)
    WITH READ ONLY;

Answer 1

The first step in diagnosing a problem when creating a view is to try the select part on its own. In this case you would still get the ORA-00942 error, but the problem is now just a query and access issue and not to do with the view specifically.

When you get ORA-00942: table or view does not exist, it's because either:

1. The table or view name that you typed really doesn't exist.

   • Check the spelling - maybe there is a typo.

   • Are you connected to a database where it exists? Perhaps you are on a test system that doesn't have it.

   • Query dba_objects to see whether the table exists in another schema. (If you don't have privileges to query dba_objects, all_objects lists everything you have permission to view, which may be some help.)

2. It really does exist, but it's in another schema.
   In that case, there are two possible issues:

   • You don't have permission to query it. The table's owner needs to grant read on xyz (substitute the actual table name for xyz) to either

     • you

     • public (if you want everyone to be able to see the data, not always advisable)

     • a role that you have (but roles aren't used by stored PL/SQL or views, though, so it's possible that you can query a table in another schema thanks to a role that you have, but still not be able to create a view or a procedure that uses it.)

   • You need to specify the schema. Say you want to query the REGIONS table in HR but you are connected as SCOTT. If you just select * from regions it will look for SCOTT.REGIONS, which doesn't exist. To fix that, do one of the following:

     • use hr.regions explicitly in your query.

     • in your schema, create or replace synonym regions for hr.regions;
       Now whenever you refer to regions, the database will automatically redirect to hr.regions.

     • in any schema with permission to create public synonyms:
       create or replace public synonym regions for hr.regions;
       Now everyone connecting to the database will have any references to regions redirected to hr.regions, which isn't always a good idea, but it's one option anyway.

     • alter session set current_schema = hr;
       Now the default schema for resolving names of objects is HR and not the one you logged into. For applications that always log in as a different user than the one that owns the tables, you can create an after logon trigger so this is always set. Then they can just refer to regions etc without needing to specify any schema and without any synonyms.

Answer 2

My issue has been resolved. :-)

I have made the following changes,

FROM
        hfdora.energization_info, 
        hfdora.trigger_info trigger_info_A,
        hfdora.trigger_info trigger_info_B, 
        hfdora.oms_consumer

Now the same view is created for the user cis.