How to Fix PLS-00905: Object Is Invalid in Oracle

PLS-00905 is raised when PL/SQL code calls a stored procedure, function, or package that is INVALID and cannot be compiled again. Objects become invalid when something they depend on changes or disappears, such as a dropped table or a changed column. Oracle tries to recompile an invalid object when it is used; PLS-00905 means that recompilation failed. Find the compile errors in USER_ERRORS and fix the cause.

Reproduce the Error

A procedure reads a table, and the table is dropped:

Example:

create table demo_report_rows (line_no number, txt varchar2(100));
create or replace procedure demo_print_report is
begin
  for r in (select txt from demo_report_rows order by line_no) loop
    dbms_output.put_line(r.txt);
  end loop;
end;
/
drop table demo_report_rows purge;        -- the procedure now depends on a missing table
begin
  demo_print_report;
end;
/

Output:

Table DEMO_REPORT_ROWS created.

Procedure DEMO_PRINT_REPORT compiled

Table DEMO_REPORT_ROWS dropped.

  demo_print_report;
  *
ERROR at line 2:
ORA-06550: line 2, column 3:
PLS-00905: object NIMBUS.DEMO_PRINT_REPORT is invalid
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

Fix: Find the Errors, Then Restore What Is Missing

Example:

select object_name, status from user_objects where object_name = 'DEMO_PRINT_REPORT';
alter procedure demo_print_report compile;
select line, position, text from user_errors where name = 'DEMO_PRINT_REPORT' order by sequence;
-- restore what it depends on, then compile again
create table demo_report_rows (line_no number, txt varchar2(100));
alter procedure demo_print_report compile;
select object_name, status from user_objects where object_name = 'DEMO_PRINT_REPORT';

Output:

OBJECT_NAME          STATUS
____________________ __________
DEMO_PRINT_REPORT    INVALID

Warning: ORA-17110: Execution completed with warning.
https://docs.oracle.com/error-help/db/ora-17110/

Procedure DEMO_PRINT_REPORT altered.

   LINE    POSITION TEXT
_______ ___________ ____________________________________________________
      3          29 PL/SQL: ORA-00942: table or view does not exist
      3          13 PL/SQL: SQL Statement ignored
      4          26 PLS-00364: loop index variable 'R' use is invalid
      4           5 PL/SQL: Statement ignored

Table DEMO_REPORT_ROWS created.

Procedure DEMO_PRINT_REPORT altered.

OBJECT_NAME          STATUS
____________________ _________
DEMO_PRINT_REPORT    VALID

• ALTER PROCEDURE ... COMPILE completes with a warning, and USER_ERRORS shows why: ORA-00942 for the missing table on line 3.
• After the table is back, the next compile succeeds and the status is VALID.

Things to Know

• USER_DEPENDENCIES shows what an object depends on, which helps find the change that invalidated it.
• DBMS_UTILITY.COMPILE_SCHEMA or UTL_RECOMP recompiles all invalid objects of a schema after a deployment.
• SHOW ERRORS in SQL*Plus or SQLcl displays the errors of the last compiled object.

Related Guides

• How to Compile All Invalid Packages in Schema?
• How to Fix PLS-00302: Component Must Be Declared in Oracle

Conclusion

PLS-00905 means the called object is invalid and does not compile. Read its errors in USER_ERRORS, restore or adjust what it depends on, and compile it again.