一个ORA-604错误的分析

同事碰到一个ORA-604错误，分析了一下发觉还比较有趣，简单记录一下。

 

 

出错的SQL大致如下：

SQL> CREATE TABLE T_604 AS
  2  SELECT * FROM
  3  (SELECT OBJECT_TYPE, TO_CHAR(AVG(OBJECT_ID), '999999.999') FROM DBA_OBJECTS
  4  GROUP BY OBJECT_TYPE
  5  ORDER BY 2 DESC)
  6  WHERE ROWNUM < 10;
(SELECT OBJECT_TYPE, TO_CHAR(AVG(OBJECT_ID), '999999.999') FROM DBA_OBJECTS
                                                                *
第 3 行出现错误:
ORA-00604: 递归 SQL 层 1 出现错误
ORA-01401: 插入的值对于列过大

由于同事并不是DBA，因此对这个错误比较困惑，他不清楚为什么SELECT语句执行没有任何的问题，而根据SELECT的查询结果去创建表就发生了错误，因此同事任何可能是空间分配上出了问题。

SQL> SELECT * FROM
  2  (SELECT OBJECT_TYPE, TO_CHAR(AVG(OBJECT_ID), '999999.999') FROM DBA_OBJECTS
  3  GROUP BY OBJECT_TYPE
  4  ORDER BY 2 DESC)
  5  WHERE ROWNUM < 10;

OBJECT_TYPE        TO_CHAR(AVG
------------------ -----------
DATABASE LINK
MATERIALIZED VIEW    31618.000
RULE SET             31581.400
DIMENSION            31414.000
DIRECTORY            31207.667
EVALUATION CONTEXT   29200.091
XML SCHEMA           28358.700
TRIGGER              27552.375
INDEXTYPE            27381.750

已选择9行。

一般来说，ORA-604错误很少直接出现在用户调用的SQL中，对于这种情况，后面的那个错误信息是真正引发错误的原因。

所以这里引发错误的真正原因是后面的那个ORA-1401错误。这个错误不难理解，插入的值比列的定义要大。

不过CREATE TABLE AS SELECT无法为创建表的列指定数据类型和长度限制，数据类型和长度都由SELECT的查询结果来确定。按照道理就不应该会出现这种错误。

其实问题很简单，导致错误的真正原因是列名长度太长了，只需要将上面的CREATE TABLE语句改变一下写法，就可以顺利执行了：

SQL> CREATE TABLE T_604 (OBJECT_TYPE, AVG_OBJECT_ID) AS
  2  SELECT * FROM
  3  (SELECT OBJECT_TYPE, TO_CHAR(AVG(OBJECT_ID), '999999.999') FROM DBA_OBJECTS
  4  GROUP BY OBJECT_TYPE
  5  ORDER BY 2 DESC)
  6  WHERE ROWNUM < 10;

表已创建。

SQL> DROP TABLE T_604;

表已删除。

SQL> CREATE TABLE T_604 AS
  2  SELECT * FROM
  3  (SELECT OBJECT_TYPE, TO_CHAR(AVG(OBJECT_ID), '999999.999') AVG_OBJECT_ID
  4  FROM DBA_OBJECTS
  5  GROUP BY OBJECT_TYPE
  6  ORDER BY 2 DESC)
  7  WHERE ROWNUM < 10;

表已创建。

当用户执行DDL操作时，Oracle通过大量的递归调用来维护数据字典。比如这个CREATE TABLE语句，Oracle就会更新TAB$、COL$等表。这些操作都是递归调用操作，而在递归调用过程中出现的错误，就会报错ORA-604。

由于没有指定别名，Oracle试图将TO_CHAR(AVG(OBJECT_ID), '999999.999')作为列名，而这个的长度显然超过了列长度30的限制，因此Oracle在插入数据字典表的时候报错ORA-1401错误。

这个错误的产生还有一定的条件，如果是TO_CHAR(AVG(OBJECT_ID), '999999.999')直接出现在SELECT的外层，在CREATE TABLE的时候，Oracle会明确要求用户提供别名：

SQL> CREATE TABLE T_604 AS
  2  SELECT OBJECT_TYPE, TO_CHAR(AVG(OBJECT_ID), '999999.999')
  3  FROM DBA_OBJECTS
  4  GROUP BY OBJECT_TYPE
  5  ORDER BY 2 DESC;
SELECT OBJECT_TYPE, TO_CHAR(AVG(OBJECT_ID), '999999.999')
                    *
第 2 行出现错误:
ORA-00998: 必须使用列别名命名此表达式

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-374849/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-374849/