ORA-04063 - Re-compiling PL/SQL procedures and packages

Free Oracle Tips

HTML Text

Home
E-mail Us
Oracle Articles

Oracle Training
Oracle News
Oracle Forum
Class Catalog

Our Staff
Our Prices
Help Wanted!

Remote DBA
Oracle Tuning
Emergency 911
RAC Support
Apps Support
Analysis
Design
Implementation
Oracle Support

SQL Tuning
Security
UNIX
Oracle UNIX
Linux
Oracle Linux
Monitoring
Remote help
Remote plans
Remote services
Oracle C++
Oracle Java
Apache
JDeveloper
App Server
Applications
Oracle Forms
Oracle Portal
11i Upgrades
SQL Server
Oracle Concepts
HTML-DB Tips
Software Help
Remote Help
Development
Implementation

Financials Training
Oracle 11i
Oracle Apps 11i
Oracle Workflow
Oracle AR 11i Class
Oracle AP 11i class
Oracle GL 11i class
Oracle HR 11i class
Oracle FA 11i class
11i Project Mgt
11i procurement
11i collections

Oracle Posters
Oracle Books
Oracle Tuning Book
Oracle RAC Book
Oracle Security
Easy Oracle Books
Oracle Scripts
SQL Server DBA
SQL Design Patterns
WISE
Excel-DB

BC Oracle News

Rednecks!
Dress code
Arabian Stallion
Burleson Arabians
Guide Horses
Don Burleson Blog
Golf & Travel

Privacy Policy

ORA-04063 - Re-compiling PL/SQL procedures and packages

Don Burleson

The ORA-04063 error is caused when a PL/SQL procedure of package becomes invalid after a change to a subordinate procedure or a schema (table) change.

ORA-04063: package body "xxx" has errors

I start by trying to re-compile the package and see what the error is:

ALTER PROCEDURE | FUNCTION| PACKAGE [<schema>.] <name> COMPILE [BODY]

I use a script to recompile PL/SQL packages. Because compiling PL/SQL involves getting all PL/SQL within other PL/SQL procedures, you may need to run this several times to successfully compile all of the PL/SQL:

Set heading off;
set feedback off;
set echo off;
Set lines 999;

Spool run_invalid.sql

select
'ALTER ' || OBJECT_TYPE || ' ' ||
OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from
dba_objects
where
status = 'INVALID'
and
object_type in ('PACKAGE','FUNCTION','PROCEDURE')
;

spool off;

set heading on;
set feedback on;
set echo on;

@run_invalid.sql

For more Oracle PL/SQL scripts, I recommend Mike Ault's code depot of over 400 Oracle scripts:

www.oracle-script.com

Need an Oracle Health Check?

Do you have bad performance after an upgrade?
Need to certify that your database follows best practices?

BC Oracle performance gurus can quickly certify every aspect of your Oracle database and provide a complete verification that your database is fully optimized.

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.
Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata? Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail: and include the URL for the page.

Burleson Consulting

The Oracle of database support

Oracle® is the registered trademark of Oracle Corporation.