ORA-00972: 标识符过长问题解决方案

时间:2024-10-20 17:44:53

今天从数据库导数据时,遇到了一个"ORA-00972: 标识符过长"错误,特此记录解决方案如下。
1、报错截图
在这里插入图片描述
2、原因分析及解决方案
由于字段别名过长导致,将字段别名缩减即可解决报错
比如我的原始SQL如下:

SELECT ORDER_NUM AS "申请单号",
  EMP_NAME AS "申请人",
  CREATE_TIME AS "申请日期",
  EXTRACTVALUE(DOCUMENT_DATA, '/root/empCompany')  AS "所属公司",
  EXTRACTVALUE(DOCUMENT_DATA, '/root/empDept')     AS "所属部门",
  EXTRACTVALUE(DOCUMENT_DATA, '/root/IT_danger')   AS "需求方案风险及技术说明"                    
FROM dat_document d
WHERE d.form_name ='FM_IT19_01'
AND d.ORDER_NUM   = 'IT1920200828002'
AND TO_CHAR(d.create_time,'yyyy-MM-dd') BETWEEN '2020-01-01' AND '2020-10-31';

将"需求方案风险及技术说明"缩减成"需求方案风险"即可
修改后SQL如下:

SELECT ORDER_NUM AS "申请单号",
  EMP_NAME AS "申请人",
  CREATE_TIME AS "申请日期",
  EXTRACTVALUE(DOCUMENT_DATA, '/root/empCompany')  AS "所属公司",
  EXTRACTVALUE(DOCUMENT_DATA, '/root/empDept')     AS "所属部门",
  EXTRACTVALUE(DOCUMENT_DATA, '/root/IT_danger')   AS "需求方案风险"                    
FROM dat_document d
WHERE d.form_name ='FM_IT19_01'
AND d.ORDER_NUM   = 'IT1920200828002'
AND TO_CHAR(d.create_time,'yyyy-MM-dd') BETWEEN '2020-01-01' AND '2020-10-31';

3、总结
Oracle中表名,列名,标识列字符不能超过30个字符。
其中中文不能超过10个,字符不能超过30个。

参考文章
/godtrue/p/