ORA-06502:PL/SQL:数字或值错误:字符到数字的转换错误-to_DATE错误

Question

我有一个触发器语句，要在其中比较两个日期值并减去它们。当我尝试这样做时，我会得到错误ORA-06502:PL/SQL:数字或值错误：字符到数字的转换错误

这是触发代码和我试图做的插入

CREATE OR REPLACE TRIGGER BALANCE_FEE
AFTER INSERT OR UPDATE OR DELETE ON CHARTERS
FOR EACH ROW 
DECLARE
FEE NUMBER;
ACL_DATE DATE;
EXP_DATE DATE;
GRP_ID NUMBER;
BEGIN
ACL_DATE := :NEW.ACL_RETURN_DATE;
EXP_DATE := :NEW.EXP_RETURN_DATE;
GRP_ID := :NEW.GRP_ID;

IF ACL_DATE > EXP_DATE
THEN
FEE := (ACL_DATE - EXP_DATE) * 75;
IF ACL_DATE < EXP_DATE
THEN
FEE := (ACL_DATE - EXP_DATE)* -20;
ELSE
FEE := 0;
END IF;
END IF;
UPDATE CUSTOMER
SET CUSTOMER.BALANCE = CUSTOMER.BALANCE + FEE
WHERE CUSTOMER.GRP_ID = GRP_ID;
END;
/
SHOW ERROR;

以下是我要做的插入语句。

INSERT INTO CHARTERS (CHARTER_ID,BOAT_ID,EXP_RETURN_DATE,ACL_RETURN_DATE,GRP_ID) VALUES ('T001','B001',TO_DATE ('2019/01/20', 'yyyy/mm/dd'),TO_DATE ('2019/01/20', 'yyyy/mm/dd'),'G002');
INSERT INTO CHARTERS (CHARTER_ID,BOAT_ID,EXP_RETURN_DATE,ACL_RETURN_DATE,GRP_ID) VALUES ('T002','B002',TO_DATE ('2019/03/10', 'yyyy/mm/dd'),TO_DATE ('2019-03/08', 'yyyy/mm/dd'),'G001');
INSERT INTO CHARTERS (CHARTER_ID,BOAT_ID,EXP_RETURN_DATE,ACL_RETURN_DATE,GRP_ID) VALUES ('T003','B003',TO_DATE ('2019/05/05', 'yyyy/mm/dd'),TO_DATE ('2019/05/07', 'yyyy/mm/dd'),'G003');

如果有帮助的话，这里是租船表，如果你认为这可能与此有关。

CREATE TABLE CHARTERS (
CHARTER_ID VARCHAR(20),
BOAT_ID VARCHAR(20) REFERENCES BOAT(BOAT_ID),
GRP_ID VARCHAR(20) REFERENCES CUSTOMER(GRP_ID),
EXP_RETURN_DATE DATE,
ACL_RETURN_DATE DATE);

这是我在尝试运行所有三个插入语句时得到的错误代码

INSERT INTO CHARTERS (CHARTER_ID,BOAT_ID,EXP_RETURN_DATE,ACL_RETURN_DATE,GRP_ID) VALUES ('T003','B003',TO_DATE ('2019/05/05', 'yyyy/mm/dd'),TO_DATE ('2019/05/07', 'yyyy/mm/dd'),'G003')
Error report -
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "ADMIN_BF.BALANCE_FEE", line 11
ORA-04088: error during execution of trigger 'ADMIN_BF.BALANCE_FEE'

Answer 1

在表中，GRP_ID的数据类型为VARCHAR(20)，在触发器中，将其分配给Number变量。

您需要更新触发器以更改变量GRP_ID的数据类型

更换

GRP_ID     NUMBER;

带有

GRP_ID     VARCHAR2(20);