Solved – How to resolve ORA-04063 “%s has errors”?

Question: What is the cause of the error ORA-04063 “%s has errors”? How do I resolve this?

Oerr utility returns this for the ORA error.

ORA-04063 "%s has errors"
Attempt to execute a stored procedure or use a view that has errors. For stored procedures, the problem could be syntax errors or references to other, non-existent procedures. For views, the problem could be a reference in the view's defining query to a non-existent table. Can also be a table which has references to non-existent or inaccessible types.

 

Solution

There was an attempt to access an invalid view or a procedure.

The view could have been created with the FORCE option. An operation like a GRANT SELECT against this invalid view will return the ORA-04063 error.

In the case below tab1 table does not exist. The view is created since the FORCE option is use.

Consequently when the GRANT SELECT is issued against this invalid view the ORA-04063 error is returned.

SQL>CREATE OR REPLACE FORCE VIEW vst.myview
AS SELECT * FROM tab1;

Warning: View created with compilation errors.

SQL> GRANT SELECT ON vst.myview TO USER_A;

ORA-04063: view "VST.MYVIEW" has errors

When the view is created on an existing table the GRANT SELECT does not return the ORA-04063 error message.

SQL>CREATE OR REPLACE FORCE VIEW vst.myview
AS SELECT * FROM dba_tables;

View created.

SQL> GRANT SELECT ON vst.myview TO USER_A;

Grant succeeded.

Issue resolved!

Related OraTips

Learn about Deprecated Database Parameters in Oracle 12c – When upgrading to Oracle 12c it’s good to be familiar with these parameters. Read more here.
Proxy User Authentication with Connect Through in Oracle Database – The Proxy authentication mechanism allows a user to login as their own user but land into a different schema without knowing the password for that user.

Other Articles

Setting up Oracle 12c Active Data Guard Database In the Multitenant world, the PDB Database is considered an independent database but the operational tasks performed at the CDB level effect all the PDB databases plugged into it. The task of setting up of a Data Guard, switching or failing over are all performed at the CDB level. When the Data Guard is initially setup, with the CDB database, all PDB’s are also replicated to the target. Read more here.
Connecting Applications from Desktop to your VirtualBox Database If you are trying to connect between VirtualBox Machines or trying to setup the network to communicate from the Desktop to the VirtualBox machines, follow the simple to use instructions here


Note: This Oracle documentation was created for reference for use by Vitalsofttech DBA’s. If you have any questions please post by clicking on the ASK A QUESTION link above.

Leave a Reply

Your email address will not be published.