ORA-04063 is raised when code calls a package, or another stored object, that was saved but did not compile. CREATE PACKAGE BODY stores the code even when it has errors, so the problem shows up later, at the first call. The message names the object, not the error; the compile errors are in USER_ERRORS. Fix them and compile the package body again.
Reproduce the Error
Example:
create or replace package demo_tax_pkg is
function with_tax (p_amount number) return number;
end;
/
create or replace package body demo_tax_pkg is
function with_tax (p_amount number) return number is
begin
return p_amount * (1 + v_rate); -- V_RATE is not declared
end;
end;
/
select demo_tax_pkg.with_tax(100) from dual;Output:
Package DEMO_TAX_PKG compiled Package Body DEMO_TAX_PKG compiled Errors for PACKAGE BODY DEMO_TAX_PKG: LINE/COL ERROR -------- ------------------------------------------------------------- 4/5 PL/SQL: Statement ignored 4/28 PLS-00201: identifier 'V_RATE' must be declared Error starting at line : 12 In command - select demo_tax_pkg.with_tax(100) from dual Error at Command Line : 12 Column : 40 Error report - SQL Error: ORA-04063: package body "NIMBUS.DEMO_TAX_PKG" has errors
SQLcl lists the compile errors right after the CREATE, but the body is stored, and the call fails with ORA-04063.
Fix: Read USER_ERRORS and Fix the Body
Example:
select line, position, text from user_errors
where name = 'DEMO_TAX_PKG' and type = 'PACKAGE BODY' order by sequence;
create or replace package body demo_tax_pkg is
c_rate constant number := 0.18;
function with_tax (p_amount number) return number is
begin
return p_amount * (1 + c_rate);
end;
end;
/
select demo_tax_pkg.with_tax(100) as total from dual;Output:
LINE POSITION TEXT
_______ ___________ __________________________________________________
4 28 PLS-00201: identifier 'V_RATE' must be declared
4 5 PL/SQL: Statement ignored
Package Body DEMO_TAX_PKG compiled
TOTAL
________
118Things to Know
- ORA-04063 also names views ("view ... has errors") and other stored objects that failed to compile.
- Check scripts for compile errors at deploy time, for example by querying USER_ERRORS or USER_OBJECTS with STATUS = 'INVALID' after they run.
- When the specification is valid but the body is not, callers that compile against the specification stay valid, so the error appears only at run time.
Related Guides
Conclusion
ORA-04063 means the called package body was stored with compile errors. Read them in USER_ERRORS, correct the code, compile again, and check for invalid objects after every deployment.
