ORA-01407错误求助：PeopleSoft中无法更新CLASS_FLD为NULL

问题描述 #

错误信息：

- ORA-01407: cannot update ("PSOWNER"."PS_VCHR_LINE_STG"."CLASS_FLD") to NULL Failed SQL stmt: UPDATE

在PeopleSoft生成报表时，系统提示“NO Success”，对应的App Engine UPDATE语句代码如下：

UPDATE %Table(VCHR_LINE_STG) A 
  SET A.CLASS_FLD = ( 
 SELECT SUBSTR(DCP_FLD49 
 ,3 
 ,4) 
  FROM %Table(DCP_AP11_TMP2) 
 WHERE VCHR_BLD_KEY_C1 = A.VCHR_BLD_KEY_C1 
   AND DCP_FLD34= A.VOUCHER_LINE_NUM),A.BUSINESS_UNIT =( 
 SELECT D.CF_ATTRIB_VALUE 
  FROM %Table(CF_ATTRIB_TBL) D 
  , %Table(DEPT_TBL) E 
 WHERE ( D.EFFDT = ( 
 SELECT MAX(D_ED.EFFDT) 
  FROM %Table(CF_ATTRIB_TBL) D_ED 
 WHERE D.SETID = D_ED.SETID 
   AND D.CHARTFIELD_VALUE = D_ED.CHARTFIELD_VALUE 
   AND D_ED.EFFDT <= SYSDATE) 
   AND E.EFFDT=D.EFFDT 
   AND D.CHARTFIELD_VALUE = ( 
 SELECT M.DCP_FLD41 
  FROM %Table(DCP_AP11_TMP2) M 
 WHERE M.VCHR_BLD_KEY_C1 = A.VCHR_BLD_KEY_C1 
   AND M.DCP_FLD34= A.VOUCHER_LINE_NUM) 
   AND D.SETID = E.SETID 
   AND D.SETID = 'DCPID' 
   AND D.CF_ATTRIBUTE='AP_BUSN_UNIT' 
   AND E.EFFDT = ( 
 SELECT MAX(E_ED.EFFDT) 
  FROM %Table(DEPT_TBL) E_ED 
 WHERE E.SETID = E_ED.SETID 
   AND E.DEPTID = E_ED.DEPTID 
   AND E_ED.EFFDT <= SYSDATE) 
   AND E.DEPTID = D.CHARTFIELD_VALUE 
   AND E.SETID = D.SETID 
   AND E.EFF_STATUS='A')),A.BUSINESS_UNIT_GL=( 
 SELECT D.CF_ATTRIB_VALUE 
  FROM %Table(CF_ATTRIB_TBL) D 
  , %Table(DEPT_TBL) E 
 WHERE ( D.EFFDT = ( 
 SELECT MAX(D_ED.EFFDT) 
  FROM %Table(CF_ATTRIB_TBL) D_ED 
 WHERE D.SETID = D_ED.SETID 
   AND D.CHARTFIELD_VALUE = D_ED.CHARTFIELD_VALUE 
   AND D_ED.EFFDT <= SYSDATE) 
   AND E.EFFDT=D.EFFDT 
   AND D.CHARTFIELD_VALUE = ( 
 SELECT M.DCP_FLD41 
  FROM %Table(DCP_AP11_TMP2) M 
 WHERE M.VCHR_BLD_KEY_C1 = A.VCHR_BLD_KEY_C1 
   AND M.DCP_FLD34= A.VOUCHER_LINE_NUM) 
   AND D.SETID = E.SETID 
   AND D.SETID = 'DCPID' 
   AND D.CF_ATTRIBUTE='GL_BUSN_UNIT' 
   AND E.EFFDT = ( 
 SELECT MAX(E_ED.EFFDT) 
  FROM %Table(DEPT_TBL) E_ED 
 WHERE E.SETID = E_ED.SETID 
   AND E.DEPTID = E_ED.DEPTID 
   AND E_ED.EFFDT <= SYSDATE) 
   AND E.DEPTID = D.CHARTFIELD_VALUE 
   AND E.SETID = D.SETID  
   AND E.EFF_STATUS='A')) 
 WHERE EXISTS ( 
 SELECT 'X' 
  FROM %Table(DCP_AP11_TMP2) 
 WHERE VCHR_BLD_KEY_C1 = A.VCHR_BLD_KEY_C1 
   AND VOUCHER_LINE_NUM = A.VOUCHER_LINE_NUM)

问题原因 #

ORA-01407错误的核心是**CLASS_FLD字段不允许为NULL**，但当前UPDATE语句中给CLASS_FLD赋值的子查询返回了NULL值，可能的触发场景：

1. 子查询在DCP_AP11_TMP2中找不到匹配VCHR_BLD_KEY_C1和VOUCHER_LINE_NUM的记录；
2. 找到的记录中DCP_FLD49本身是NULL，或者SUBSTR(DCP_FLD49,3,4)截取后得到NULL（比如DCP_FLD49长度不足3位）。

解决方案 #

1. 排查数据问题 #

先运行以下查询定位问题记录：

-- 找出DCP_FLD49为空或长度不足的记录
SELECT A.VCHR_BLD_KEY_C1, A.VOUCHER_LINE_NUM, B.DCP_FLD49
FROM %Table(VCHR_LINE_STG) A
JOIN %Table(DCP_AP11_TMP2) B ON A.VCHR_BLD_KEY_C1 = B.VCHR_BLD_KEY_C1 AND A.VOUCHER_LINE_NUM = B.DCP_FLD34
WHERE B.DCP_FLD49 IS NULL OR LENGTH(B.DCP_FLD49) < 3;

-- 找出子查询匹配不上的记录
SELECT A.VCHR_BLD_KEY_C1, A.VOUCHER_LINE_NUM
FROM %Table(VCHR_LINE_STG) A
WHERE EXISTS (SELECT 'X' FROM %Table(DCP_AP11_TMP2) WHERE VCHR_BLD_KEY_C1 = A.VCHR_BLD_KEY_C1 AND VOUCHER_LINE_NUM = A.VOUCHER_LINE_NUM)
AND NOT EXISTS (SELECT 'X' FROM %Table(DCP_AP11_TMP2) WHERE VCHR_BLD_KEY_C1 = A.VCHR_BLD_KEY_C1 AND DCP_FLD34 = A.VOUCHER_LINE_NUM);

根据结果补全缺失数据或修正不符合要求的DCP_FLD49值。

2. 修改UPDATE语句避免NULL赋值 #

根据业务需求选择以下方式之一调整CLASS_FLD的赋值逻辑：

• 子查询返回NULL时保留原字段值：

A.CLASS_FLD = COALESCE(
  (SELECT SUBSTR(DCP_FLD49,3,4) FROM %Table(DCP_AP11_TMP2) WHERE VCHR_BLD_KEY_C1 = A.VCHR_BLD_KEY_C1 AND DCP_FLD34= A.VOUCHER_LINE_NUM),
  A.CLASS_FLD
)

• 子查询返回NULL时设置默认值：

A.CLASS_FLD = COALESCE(
  (SELECT SUBSTR(DCP_FLD49,3,4) FROM %Table(DCP_AP11_TMP2) WHERE VCHR_BLD_KEY_C1 = A.VCHR_BLD_KEY_C1 AND DCP_FLD34= A.VOUCHER_LINE_NUM),
  'DEFAULT' -- 替换为符合业务规则的默认值
)

3. 限制UPDATE范围（可选） #

如果仅需更新子查询能返回有效非NULL值的记录，可在WHERE条件中添加额外判断：

WHERE EXISTS ( 
 SELECT 'X' 
  FROM %Table(DCP_AP11_TMP2) 
 WHERE VCHR_BLD_KEY_C1 = A.VCHR_BLD_KEY_C1 
   AND VOUCHER_LINE_NUM = A.VOUCHER_LINE_NUM
)
AND EXISTS (
 SELECT 'X'
 FROM %Table(DCP_AP11_TMP2)
 WHERE VCHR_BLD_KEY_C1 = A.VCHR_BLD_KEY_C1
   AND DCP_FLD34 = A.VOUCHER_LINE_NUM
   AND DCP_FLD49 IS NOT NULL
   AND LENGTH(DCP_FLD49) >=3
)

内容的提问来源于stack exchange，提问作者KISHORE