ORA-25007: functions or methods not allowed in WHEN clause ORACLE 报错 故障修复 远程处理
文档解释
ORA-25007: functions or methods not allowed in WHEN clause
Cause: PLSQL function call or method invocation is not allowed in the WHEN clause when creating a trigger.
Action: Remove the function call or method invocation from the WHEN clause.
ORA-25007 错误信息指出，在 WHEN 子句中不允许使用函数或方法。
官方解释
ORA-25007: functions or methods not allowed in WHEN clause
Cause: An attempt to use a function or method in the WHEN clause of a trigger action was made.
Action: Create a function or procedure and place the call to the function or procedure in the trigger action.
常见案例
例如，下列触发器包含引用函数 Uppercase():
CREATE OR REPLACE TRIGGER trg_001
BEFORE INSERT OR UPDATE ON emp FOR EACH ROW
BEGIN
WHEN (Uppercase(:NEW.ename) = ‘SMITH’) THEN
:NEW.SAL := 20000;
END WHEN;
END;
/
此事例中将会引发ORA-25007错误，因为在WHEN子句内有一个函数调用，而在WHEN子句内不允许有函数调用，因此报错。
一般处理方法及步骤
1.将函数调用的逻辑抽取出来，形成一个函数或存储过程。
2.将WHEN子句内的函数调用替换成函数或存储过程的调用。
例如替换后的触发器：
CREATE OR REPLACE FUNCTION Uppercase(ename VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN UPPER(ename);
END;
/
—
CREATE OR REPLACE FUNCTION Set_Salary(ename VARCHAR2) RETURN NUMBER
IS
BEGIN
IF Uppercase(ename) = ‘SMITH’ THEN
RETURN 20000;
ELSE
RETURN 0;
END IF;
END;
/
—
CREATE OR REPLACE TRIGGER trg_001
BEFORE INSERT OR UPDATE ON emp FOR EACH ROW
BEGIN
WHEN (Set_Salary(:NEW.ename) > 0) THEN
:NEW.SAL := Set_Salary(:NEW.ename);
END WHEN;
END;
/
