ORA-04063: view “TEST.TEST_VIEW” has errors

ORA-04063: view “TEST.TEST_VIEW” has errors

Error: ORA-04063: view “TEST.TEST_VIEW” has errors

SQL> create view test_view as select * from emp;
View created.

SQL> drop table emp;
Table dropped.

SQL> grant select on test_view to test1;
grant select on test_view to test1
*
ERROR at line 1:
ORA-04063: view "TEST.TEST_VIEW" has errors

Cause:
If you create a package, procedure, function or view then some objects dropped which is used in package, procedure, function or view then you try to use them then you got the error.

Solution:
You need to verified that objects used in DDL of this objects VIEWS, PACKAGE, FUNCTION is present if not you can change according to your NEED.
1. Get the DDL of the package,view, function etc.
2. Run the DDL in SQLPLUS window for getting error.
3. SHOW ERRORS command will help you to fine real reason of error.


-- Get the DDL of the VIEW example is used in above.
set heading off;
set echo off;
Set pages 999;
set long 90000;
SELECT dbms_metadata.get_ddl('VIEW', 'TEST_VIEW') FROM DUAL;

CREATE OR REPLACE FORCE EDITIONABLE VIEW "TEST"."TEST_VIEW" ("ID", "JOININGDATE") AS select "ID","JOININGDATE" from emp;

--- You can execute the above DDL statement in SQLPLUS
SQL> CREATE OR REPLACE FORCE EDITIONABLE VIEW "TEST"."TEST_VIEW" ("ID", "JOININGDATE") AS select "ID","JOININGDATE" from emp;

Warning: View created with compilation errors.

--- You will get the compilation errors fire SHOW ERRORS in SQLPLUS
SQL> show errors
Errors for VIEW "TEST"."TEST_VIEW":

0/0 ORA-00942: table or view does not exist

Note: SHOW ERROR command give you the real problem. In my CASE table is not exists.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.