Search code examples
sqloracleplsqlstored-functions

how to fix ORA-06575: Package or function is in an invalid state error

I'm looking to create a package, already have a working procedure. Working on a function, step by step and I have encountered an

ORA-06575: Package or function PROJECT_LENGTH is in an invalid state

error.

The aim of this is to eventually be able to show how long my project lasted, from startdate to enddate in months. How do I fix this issue?

I have tried various different approaches and examples. I have checked all variables are correct.

CREATE OR REPLACE FUNCTION project_length(startDate IN DATE,
                                          endDate   IN DATE) RETURN NUMBER IS
BEGIN
  RETURN FLOOR(MONTHS_BETWEEN(startDate, endDate) / 12);
END;
/

Testing :

SELECT projectname, project_length(startDate,endDate)  
  FROM project 
 WHERE project_length(startDate,endDate) > 0;

I'm expecting an output which will consist of the projectName and Project Length, displaying the amount of months a project took

Solution

  • Just use

    ALTER FUNCTION project_length COMPILE;

    This issue occurs one of the dependent objects got a DDL on it. Perhaps you have a statement inside this function, not revealed here , referencing the table project and recently a column added to the table project (a DDL applied to the table)

    Actually there's no dependent object in the code presented here. So something must be missing in this function's code.