'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

sqloracleplsqlstored-functions


Solution 1:[1]

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.

Solution 2:[2]

I found this error also comes up when there is a syntax error in your function. Debug it step by step until it actually works. Weird that compiler does not validate the syntax when you create the function...

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 DataUnitedLtd

Related Questions