Oracle 10G: ORA-06575: function in invalid state

Question

I created a funcion like this

CREATE OR REPLACE FUNCTION tax 
(p_sal IN  NUMBER(4)) 
RETURN NUMBER 
AS
v_tax NUMBER(4);
BEGIN 
v_tax:= CASE        
WHEN p_sal> 4000 THEN
p_sal*0.33       
WHEN p_sal >2500 THEN 
p_sal*0.25      
WHEN p_sal >1500 THEN 
p_sal*0.20     
ELSE 0         
END; 
RETURN v_tax;
END;
/

when i used this tax function in insert stmt like

INSERT INTO employees(eno, ename, job, join_date, sal, comm)
VALUES (7784,'allen','salesman',sysdate, 5000, tax(5000));

it shows the error like

ERROR: ORA-O6575: package or function tax is in invalid state.

can anyone suggest me how to make this function is in valid state? thanks in advance.

Answer 1

Check errors with this command:

Select * from user_errors where name='Your function name'

Answer 2

A function is compiled like this:

alter function tax compile;

Then, check for compilation errors with:

SHOW ERRORS

There are two main reasons while an object in Oracle is invalid:

1. The code is invalid (and gave an error message when you tried to compile it). The solution is of course to fix the error and then recompile it.
2. The object references another object and the other object was changed. The solution is to recompile the invalid object.

Also, some database connection drivers keep references to objects in the database. If the state of those object change in the database the references go stale and you will get an error similar to the one above.