CODEWITHSUNDEEP
sqloracle-databaseplsqlstored-functions
Neto
Neto

Reputation: 59

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

Upvotes: 5

Views: 17225

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

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.

Upvotes: 6

DataUnitedLtd
DataUnitedLtd

Reputation: 31

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...

Upvotes: 2

Related Questions