6

I have 3 users (schemas) in my database.

User A holds table X

I created a view Y for user B and a view Z for user C. The views are identical:

 CREATE OR REPLACE FORCE EDITIONABLE VIEW "user_name"."view_name" ("field_1", "field_2") AS 
  SELECT field1_1
,      field2_2
FROM   User_A.X;

The first view works fine; for the second one I have the following error when trying to see data (from SQL developer):

ORA-04063: view "User_C.Z" has errors.

I tried to create it by SQL PLUS: it was created with compilation error, but the "show errors" command returns "no errors".

Any suggestion?

Fab
  • 1,145
  • 7
  • 20
  • 40
  • 1
    Did you really create users with case-sensitive user names? `"user_name"` is a different username than `user_name`. How did you create `user_a`? If you also created that using double quotes you might need to use `"User_A"` or `"user_a"` depending on how you created that. The same is true for the column names `field_1` is a different name than `"field_1"` –  Feb 02 '18 at 09:46

3 Answers3

13

The show errors SQL*Plus command defaults to reporting errors for the last PL/SQL object created. To show errors for a view you have to specify it:

SQL> create or replace force view someview as select * from nosuchtable;

Warning: View created with compilation errors.

SQL> show errors
No errors.

SQL> show errors view someview
Errors for VIEW SOMEVIEW:

LINE/COL   ERROR
---------- --------------------------------------------------------------------
0/0        ORA-00942: table or view does not exist
0/0        ORA-54039: table must have at least one column that is not invisible

Or you can just query user_errors:

SQL> select line, text from user_errors where name = 'SOMEVIEW' order by sequence;
LINE TEXT
---- --------------------------------------------------------------------------------
   0 ORA-54039: table must have at least one column that is not invisible
   0 ORA-00942: table or view does not exist

If you really named it something like "view_name" (in double quotes) then you need to refer to it exactly the same way, including case and quotes.

show errors view "someview"

select line, text from user_errors where name = 'someview' order by sequence;

Or, try creating the view without the force option:

SQL> create or replace view someview as select * from nosuchtable;
create or replace view someview as select * from nosuchtable
                                                 *
ERROR at line 1:
ORA-00942: table or view does not exist

Or, test the query on its own without the create view part:

SQL> select * from nosuchtable;
select * from nosuchtable
              *
ERROR at line 1:
ORA-00942: table or view does not exist
William Robertson
  • 15,273
  • 4
  • 38
  • 44
8

Oracle documentation regarding error code ORA-04063 includes the following

For views, the problem could be a reference in the view's defining query to a non-existent table

Non-existent table sometimes also means one that you don't have access to.

In whose schema did you create the view?

What privileges did you grant on the view?

What privileges did you grant on the underlying table (i.e. table X) ?

Abra
  • 19,142
  • 7
  • 29
  • 41
  • 1
    I need to add SELECT and REFERENCES privileges for USER C to the underlying table. User B already had them. – Fab Feb 02 '18 at 10:00
2

I faced the same problem normally when an attribute name is modified or deleted from any table of the view then this error occurs. I deleted a column from a table and got this error, to solve it I recreate the view after removing the attribute that exists in the view but not exists in the table

Bablu Ahmed
  • 4,412
  • 5
  • 49
  • 64