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

心已赠人 2022-12-18 15:49 1284阅读 0赞

今天从数据库导数据时，遇到了一个"**ORA-00972: 标识符过长**"错误，特此记录解决方案如下。  
**1、报错截图**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70_pic_center]  
**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个。

**参考文章**  
https://www.cnblogs.com/godtrue/p/3791591.html

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa2UxMTIzMQ_size_16_color_FFFFFF_t_70_pic_center]: /images/20221122/36f923318df347f0a84e119795205fd8.png