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

问题:

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.

回答1:

Check errors with this command:

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

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

回答3:

You can check error(s) with SHOW ERROR command

SQL> show error function Your_Function_Name;

回答4:

Make sure your function compiled without errors. That's what Oracle's telling you with ERROR: ORA-06575.

Create your function with this statement:

CREATE OR REPLACE FUNCTION tax (p_sal IN NUMBER)
   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;

You don't need the (4) on the parameter list when declaring a NUMBER in parameter.