ORA-04063: view has errors

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?

1

3 Answers

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

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) ?

1

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

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy