How To Fix “ORA-04063: Package Body Has Errors” Error In Oracle

By | January 21, 2017

How to fix ORA-04063 package body has errors error in Oracle

In Oracle, creation of an object is through providing particular command and using these changes is made into the database. During these changes, there may be compilation error within the procedure or package due to which the error prompt may be “ORA-04063: package body has errors”. In oracle a user can deploy a schema without fixing each problem with invalid objects. Unfortunately, this is not the case for views and if an error occurs in the command or within the archives then this error occurs.

Error “ORA-04063: package body has errors” in Oracle occurs because either an attempt to execute a stored procedure was made or an attempt to use a view  contains errors due to which execution landed up to errors. For stored procedures, the error may be a syntax issue or references to other procedures that do not exist.

A possible problem could be a reference that defines a query to a non-existent table. This can also be a table that has references to non-existent or inaccessible types. The information is revealed when the message gets prompted and expanded in the error utility.

You May Also Like: How to Repair ORA-01578: Oracle Data Block Corruption

The immediate solutions and action that should be takes could be:

  • shutdown immediate
  • start-up upgrade
  • run utlrp
  • run catalog and catproc scritp
  • shutdown immediate
  • startup
  • select comp_name,version,status from dba_registry;

ORA-04063: package body has errors. How to Solve?

Attempt to execute a stored procedure or use a view that has errors. For stored procedures, the problem could be syntax errors or references that are made to other data files. For views, the problem could be inaccessibility in viewing and defining query to a non-existent data. It can also be a table which has its references to non-existent or inaccessible types of statements. ORA-04063: package body has errors means that we need to recompile the package , in order to handle this alert.

By executing the PL/SQL query by itself in SQL*plus can help in resolving ORA-04063. After running the query, SQL*Plus “show error” command should be given to view the reason for this error and the location of the error.

Syntax for this command is:

SQL  > show errors

Viewing enhanced show errors command feedback, join dba_errors and dba_service so that feedback within the lines of PL/SQL source code can be viewed.

CREATE FORCE VIEW command can be chosen which will create the view but it mark it invalid. But make sure that you recreate the view after you run this command.

 

Let’s take an example, the package body PLOG in the schema LM_EMPLOY either does not exist or has a compilation error:

ORA-04063: package body “LM_EMPLOY.PLOG” has errors

In such a case where package errors arise, then connect to the database as LM_EMPLOY user and run the following command:

 SQL > alter package body plog compile;

After this use the “show error”command to see what errors need to be corrected. 

 SQL>show errors

Accessing an invalid view or procedure has been created with FORCE option. GRANT SELECT against the invalid view will return the ORA-04063 error.

In given below cae tab1 table does not exist. Since the FORCE option is used, the view is created. 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

downloadnow file buynow learn-more-button-orange

SQL> GRANT SELECT ON vst.myview TO USER_A;
It is always a good practice to double check the syntax of all your command because it is difficult to proactively prevent seeing ORA-04063. Make sure at all the mentioned dependencies are correct and all variables being referenced exist.

If this ORA-04063 still continues then contact you database administrator for assistance. Another solution may be to contact an Oracle professional to resolve this issue. Always check the Oracle consultants credentials and certification to ensure that they have relevant experience and expertise to help you out of that problem.

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, a reference in the view’s defining a query to a non-existent table could be the problem. It can also be a table which has references to non-existent or inaccessible types.