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

Question

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

Overall

Invalid objects should be automatically recompiled when referenced, suggesting there's something wrong with your function; but what you've shown is fine, and it has no dependencies to invalidate it when something else changes. Do you see an error when you run that function definition again, or when you explicitly try to compile it? I'm confused though, you refer to a package but you've shown a standalone function. (Incidentally, you probably need to swap the start/end date over in your months_between() call so you get a positive result...) – Abagail

Which client are you running these statements in, and how - are you running the function definition on its own (and seeing any errors?); and then run your test query later? Or are you running them both at once? I'm wondering if you just have an odd client or settings that are affecting the compilation phase, like / not being seen as the block terminator for instance. For instance, SQuirreL or DBeaver don't really get PL/SQL and might error after the first semicolon in the function. If so, try using a different client, like SQL Developer. – Abagail

The command SHOW ERROR FUNCTION <FUNCTION NAME> may help you find out where the problem is. If the function is ok, then that command will return No errors. – Hiddenite

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

Lemay

But then it should automatically recompile when next referenced...? Suggesting there's something else wrong and we haven't been shown the real code, maybe. Surely not... – Abagail

@AlexPoole automatical recompilation depends on a remote_dependencies_mode parameter is set to signature or not, against timestamp changes of the stored unit. By the way, As you told, better to see the whole code(I think it's incomplete). – Wick

There's no indication of any remote access here either though? But yes, agreed, something seems to be missing from the question. – Abagail

@AlexPoole yes, sure. I already meant may exists or not. that's he needs to recompile or not, that depends. – Wick

I have attempted to recompile, the error I am getting when doing so is : ORA-24344: success with compilation error. Also, this is all the function code I have written so far. This issue has really halted my progress – Overall

@Overall - then after compilation, do show errors if your client supports that, or query the user_errors view to see what is actually wrong. And add that information to your question. – Abagail

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

Redheaded

This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. To get notified when this question gets new answers, you can follow this question. Once you have enough reputation, you can also add a bounty to draw more attention to this question. - From Review – Zeitler